Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need some help with some SET Analysis.
I'm creating a KPI to sum a value for the most recent 3 months that cannot change, and then I want another KPI that shows the sum of the same value for a 3 month date range a user selects.
1st KPI
Sales = $3.5M for Feb, Mar, Apr
2nd KPI
Sales = $1.1M for Jan, Feb, Mar (3 months based on date a user selects).
When the user selects Mar, I want the 1st KPI to stay the same.
My Date field is formatted as M/D/YYYY
Here is my code:
Sum({$<[Date] ={'>=$(=AddMonths(Max([Date]),-3))<=$(=Max([Date]))'}, [Entity Category] = {'Sales'}>} [Amount])
I've tried entering the "[Date] = ", but this then cancels out my initial date range above.
I've tried entering the "[Date] = e([Date])", but this only excludes the date that is selected.
Any help would be appreciated.
May be this
Sum({$<[Date] ={'>=$(=AddMonths(Max({<[Date]>}[Date]),-3))<=$(=Max({<[Date]>}[Date]))'}, [Entity Category] = {'Sales'}>} [Amount])
May be this
1st KPI
Sum({1<[Date] ={'>=$(=Date(AddMonths(MonthStart(Max([Date])),-3),M/D/YYYY))<=$(=Date(Max([Date]),M/D/YYYY))'}, [Entity Category] = {'Sales'}>} [Amount])
2nd KPI
Sum({$<[Date] ={'>=$(=Date(AddMonths(MonthStart(Max([Date])),-3),'M/D/YYYY'))<=$(=Date(Max([Date]),M/D/YYYY))'}, [Entity Category] = {'Sales'}>} [Amount])
I had tried that too with no luck. Thanks Aar!
May be this
Sum({$<[Date] ={'>=$(=AddMonths(Max({<[Date]>}[Date]),-3))<=$(=Max({<[Date]>}[Date]))'}, [Entity Category] = {'Sales'}>} [Amount])
That did it!
I've never seen that before. At first I thought wanted me to hard code the date in there.
Thank you Sunny!
I am just ignoring selection in Date field within your inner aggregation... this might look more familiar
Sum({$<[Date] ={'>=$(=AddMonths(Max({<[Date] = >}[Date]),-3))<=$(=Max({<[Date] = >}[Date]))'}, [Entity Category] = {'Sales'}>} [Amount])
So you're doing SET Analysis inside SET Analysis.
I think I tried that before with something else and couldn't get it to work, and then just forgot about trying it again.
Thanks for your help!
So you're doing SET Analysis inside SET Analysis.
Yup!!