Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mikelutomski
New Contributor III

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

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.

1 Solution

Accepted Solutions

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

May be this

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

7 Replies
aarkay29
Valued Contributor

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

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

mikelutomski
New Contributor III

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

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

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

May be this

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

mikelutomski
New Contributor III

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

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

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

mikelutomski
New Contributor III

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

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

So you're doing SET Analysis inside SET Analysis.

Yup!!

Community Browser