Set analaysis within set analysis

[#1]

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

[sunny_talwar]

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%')

[#3]

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]

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%')

[#5]

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]

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....

[#7]

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.