Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Set analaysis within set analysis

Hi guys,

I have loaded following inline table so that I can use it as a filter.

Rolling
R3
R6
R12

I am using following expression in one of my chart

=Num(Sum(Sales) / Sum(TOTAL <SalesRep> {<Product>} Sales),'0.0%')

I would like the above expression to change based on the selections made in the Rolling field


  • if R3 is selection then it should only show sales for last 3 months from the max SalesDate in the database
  • if R6 is selection then it should only show sales for last 6 months from the max SalesDate in the database
  • if R12 is selection then it should only show sales for last 12 months from the max SalesDate in the database

What is the best IF statement or Set Analysis to achieve that?

Thanks

6 Replies
sunny_talwar
Not applicable

Re: Set analaysis within set analysis

May be like this:

=Num(Sum({<SalesDate = {"$(='>=' & Date(AddMonths(Max(SalesDate), -(PurgeChar(Rolling, 'R') * 1)), 'DateFieldFormatHere') & '<=' & Date(Max(SalesDate), 'DateFieldFormatHere'))"}>} Sales) / Sum(TOTAL <SalesRep> {<Product, SalesDate = {"$(='>=' & Date(AddMonths(Max(SalesDate), -(PurgeChar(Rolling, 'R') * 1)), 'DateFieldFormatHere') & '<=' & Date(Max(SalesDate), 'DateFieldFormatHere'))"}>} Sales),'0.0%')

Not applicable

Re: Set analaysis within set analysis

Thanks Sunny,

Its working as expected but only when one of the Rolling filter is applied.

If no Rolling filter is selected then can the expression only count following

=Num(Sum(Sales) / Sum(TOTAL <SalesRep> {<Product>} Sales),'0.0%')


?


Thanks

sunny_talwar
Not applicable

Re: Set analaysis within set analysis

May be like this:

Num(If(GetSelectedCount(Rolling) = 0,


Sum(Sales) / Sum(TOTAL <SalesRep> {<Product>} Sales),


Sum({<SalesDate = {"$(='>=' & Date(AddMonths(Max(SalesDate), -(PurgeChar(Rolling, 'R') * 1)), 'DateFieldFormatHere') & '<=' & Date(Max(SalesDate), 'DateFieldFormatHere'))"}>} Sales) / Sum(TOTAL <SalesRep> {<Product, SalesDate = {"$(='>=' & Date(AddMonths(Max(SalesDate), -(PurgeChar(Rolling, 'R') * 1)), 'DateFieldFormatHere') & '<=' & Date(Max(SalesDate), 'DateFieldFormatHere'))"}>} Sales)


,'0.0%')

Not applicable

Re: Set analaysis within set analysis

Thanks Sunny and just regarding this expression.

I am already peeking a Max Date from SalesDate column in the load script as vMaxDate.

Do you think I can utilize that variable in here?

sunny_talwar
Not applicable

Re: Set analaysis within set analysis

If this needs to change based on selection, then you probably don't want to use that because the peeked variable is hard-coded and won't change based on selections. If you don't want this to change based on selections, then go ahead....

Not applicable

Re: Set analaysis within set analysis

Hi Sunny,

Sorry for late reply but its not working

I have attached QVF file here along with the sample data in excel.

I am sure you be able to help

Thank you.