rolling 12 months doesn't work

[wanyunyang]

I'm using:

Sum({<Date={">=$(=ADDMONTHS(MAX(Date), -12))<=$(=MAX(Date))"},Year=,Month=,Quarter=>}Sales)

to calculate rolling 12 months sales (rolling from the max date within a selected time range). For example, if I select 09/20/2018-09/24/2018, I want the expression sum up sales from 09/24/2017-09/24/2018.

However, in current expression, if I select 09/20/2018-09/24/2018, it will show me sales for 09/20/2018-09/24/2018. I don't know how I can fix this expression... Please help!!

Any advice helps! Thanks in advance!!

[wanyunyang]

Hi Bala, neither works... Same problem with my expression.

[Anil_Babu_Samineni]

Try this?

Sum({<Date={">=$(=Date(ADDMONTHS(MAX(Date), -12),'Your Format Here'))<=$(=Date(MAX(Date),'Your Format Here'))"},Year=,Month=,Quarter=, Week, Period=>}Sales)

For sure, You don't really need to put anything for testing.

[wanyunyang]

Gave it a go. Same problem...

If I don't select the date range, it works. But if I select 09/20/2018-09/24/2018, it still shows me sales for 09/20/2018-09/24/2018.

[cchristenson2]

EDIT: Wanyun looking for it to be dynamic. Removed the master calendar. 

Hi Wanyun,

I would create a master calendar so you can leverage the fields and info within your set analysis. This also allows you to ignore selections in any field (Example:  Sum({< FieldName = >} Sales) )

The chart would update for selections made in other fields such as departments, people, whatever is in your data. Maximizes your flexibility.

[brianhardin]

wanyunyang‌

Sum({<Date={">=$(=ADDMONTHS(MAX(Date), -12))<=$(=MAX(Date))"},Year=,Month=,Quarter=>}Sales)

If I am reading this correctly, you want the Rolling 12 months to be dynamic where you select a date and the 12 months calculate based off that date selection.

I don't think this is possible with Set Analysis unless you try to pass a variable value into the formula.  Based on the current formula, when you use your selection criteria it filters that data and you lose the 12 Rolling Piece.  If you Add Date = into the formula then the 12 month rolling calculation will work but you lose the dynamic start point.

Did you really want the calculation to be dynamic when selecting a date?

[wanyunyang]

Hi Brian, yes, user wants to do it dynamically...

[wanyunyang]

Hi Croix, thank you for replying! But I'm not able to change the script... Sorry!

[cchristenson2]

Brian is correct, if you trim your data set to those 4 days then the calculation will only process for those 4 days.

You could try using an if statement along the lines of the following and add back in your exclusion of the fields year, month and quarter. Here is an example that I had working in a sample app with fake data.

=If(

GetSelectedCount([Discharge Date])>1,

'please select 1 day',

Count({<[Discharge Date]={">=$(=ADDMONTHS(MAX([Discharge Date]), -12))<=$(=MAX([Discharge Date]))"}>}PAT_ENC_CSN_ID)

)

[wanyunyang]

Hi Croix, I tried this as well, it's giving me sales on that particular date.

[cchristenson2]

Can you attach a sample QVW file?

I have a working example:

If user selects more than 1 date: