6 Replies Latest reply: Jan 31, 2017 9:06 PM by Lokesh Patel

# 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

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

• ###### 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

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

• ###### 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?

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

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