topic Re: create date range variable for rolling 13 months in App Development

create date range variable for rolling 13 months

jtay — Mon, 01 May 2017 13:04:35 GMT

I am working on a chart that needs to show a default view of the last 13 months.

I want to reuse the same 13 month range for other visualizations so I’ll use a variable.

I think my set analysis should look like this:

Count({<$(vAriable)>}Distinct Field1)

I have the begin and end dates calculating correctly – when viewed through a KPI.

date(MonthName(addmonths(vToday),-13)) gives me April 1, 2016 (which is correct)

date(MonthEnd(vToday)) gives me May 31, 2017 (which is correct)

vToday = Today()

I now need to put the range together in a format that will work in the variable – but I need some help please. thanks

This is where I am now…

{">$(=date(MonthName(addmonths(vToday),-13))) <=$(=date(MonthEnd(vToday)))"}

Re: create date range variable for rolling 13 months

aarkay29 — Mon, 01 May 2017 13:45:05 GMT

May be this as variable

vTest='>'& (date(MonthStart(addmonths(Today(),-13)))) &'<='&(date(MonthEnd(Today())))

sum({<[Order Date]={"$(=$(vTest))"}>}Sales)

Re: create date range variable for rolling 13 months

chriscammers — Mon, 01 May 2017 13:48:25 GMT

What you have there is a little bit of tricky syntax. I have found that you have to calculate the entire string, you cannot just rely on the dollar sign expansion to concatenate the string together ">$()<=$()" syntax does not work. so something like this should do the trick... $(='>' & date(MonthName(addmonths(vToday),-13)) & '<=' & date(MonthEnd(vToday)))

I find that with this kind of expression you also need to make sure other calendar selections are not conflicting with the 13 month set analysis.

Re: create date range variable for rolling 13 months

jtay — Thu, 18 May 2017 16:15:24 GMT

Thanks Chris - your last sentence gets to the root of the problem. We need to be able to select dates within and also outside that 13 month range and have the graph update. This is what I ended up putting together:

My variable $(vRolling13) = MonthSequence = {">$(=max(MonthSequence)-14)<=$(=max(MonthSequence))"}

Monthsequence is just a number count assigned to each month in the master calendar.

Count({<$(vRolling13) >} Distinct Value1)

thanks!