topic Re: Use Date range in SET Analysis, yet ignore a Date selection in App Development

Use Date range in SET Analysis, yet ignore a Date selection

mikelutomski — Wed, 12 Jul 2017 13:45:38 GMT

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.

Re: Use Date range in SET Analysis, yet ignore a Date selection

aarkay29 — Wed, 12 Jul 2017 13:58:06 GMT

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])

Re: Use Date range in SET Analysis, yet ignore a Date selection

mikelutomski — Wed, 12 Jul 2017 14:05:02 GMT

I had tried that too with no luck. Thanks Aar!

Re: Use Date range in SET Analysis, yet ignore a Date selection

sunny_talwar — Wed, 12 Jul 2017 14:08:00 GMT

May be this

Sum({$<[Date] ={'>=$(=AddMonths(Max({<[Date]>}[Date]),-3))<=$(=Max({<[Date]>}[Date]))'}, [Entity Category] = {'Sales'}>} [Amount])

Re: Use Date range in SET Analysis, yet ignore a Date selection

mikelutomski — Wed, 12 Jul 2017 14:14:53 GMT

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!

Re: Use Date range in SET Analysis, yet ignore a Date selection

sunny_talwar — Wed, 12 Jul 2017 22:41:36 GMT

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])

Re: Use Date range in SET Analysis, yet ignore a Date selection

mikelutomski — Thu, 13 Jul 2017 13:34:53 GMT

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!

Re: Use Date range in SET Analysis, yet ignore a Date selection

sunny_talwar — Thu, 13 Jul 2017 13:35:50 GMT

So you're doing SET Analysis inside SET Analysis.

Yup!!