MicroStrategy calculate date from prompt

06 Jan 2023

Dificulty ★★★☆☆

INTRO

This case shows example how to caclucate dates from database with date from prompt without seperate date table.

First lets create some basic data

CREATE VIEW F_Data_V AS
SELECT CAST('2000-01-01' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2023-01-01' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2023-01-02' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2023-01-03' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2023-01-04' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2023-01-05' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2022-12-01' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2022-12-31' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue UNION
SELECT CAST('2022-12-30' AS DATE) AS Day_Date , CONVERT(DECIMAL(10,2),RAND(CHECKSUM(NEWID())) * 10000) AS Revenue 

Then register atribute and metric normaly

Create a new value prompt

Select date and time

Enter name of prompt

Save prompt

Register new metric

ApplySimple("max(#0)"; ?[Comparison date]; Revenue)

?[Comparison date] is created prompt
Revenue is rendom metric (needed for FROM statement)

Convert date atribute into metric

Do not forget to set it to MAX

Calculate difference

DateDiff(Date; [Comparison date]; "d")

Example

And now we can set clusters of data

Case((DateDiff < 0); Revenue; 0)				Less then selected
Case((DateDiff = 0); Revenue; 0)				Selected date
Case(((DateDiff >=  1) And (DateDiff <= 30)); Revenue; 0)	Between 1-30
Case(((DateDiff >= 31) And (DateDiff <= 60)); Revenue; 0)	Between 31-60
Case(( DateDiff >= 61); Revenue; 0)				Greater then 60