Filters not working with Set Analysis

[#1]

Hi

The following is my set analysis:

=Sum( {1 < SalesChannel = {Retail},  RetGrpCalendarYearMonth = {"$(#vMaxYearMonth)"}>} NettSalesQty)/

count(DISTINCT {1 <  SalesChannel = {Retail}, RetGrpCalendarYearMonth = {"$(#vMaxYearMonth)"}>}IssueDate)

Im trying to achieve getting the average sales by day per max month by default or the max month chosen.

The formula above works and it gives me what i need. However, if i choose any other filter the amounts dont change.

It keeps giving me the amounts for the max month and does not drill down if i choose any filter.

Any idea what the problem might be?

Magen

[tresesco · Accepted solution]

Remove '1' and try like:

=Sum( {< SalesChannel = {Retail},  RetGrpCalendarYearMonth = {"$(#vMaxYearMonth)"}>} NettSalesQty)/

count(DISTINCT {<  SalesChannel = {Retail}, RetGrpCalendarYearMonth = {"$(#vMaxYearMonth)"}>}IssueDate)

[#3]

Thanks mate!

Below is the next set analysis - here im trying to always get the previous months sales vs current month or what ever month is chosen.

Like before it works until i try to use a filter.

I removed the 1's but still nothing.

=Sum( {  < SalesChannel = {Retail},   RetGrpCalendarYearMonth = {"$(#vPreviousMonth)"}>} NettSalesQty)/

count(DISTINCT {  < SalesChannel = {Retail}, RetGrpCalendarYearMonth = {"$(#vPreviousMonth)"}>}IssueDate)

Magen

[tresesco]

It could be because of your varaible vPreviousMonth. How do you define it?

[ashfaq_haseeb]

Hi try below

=Sum( {< SalesChannel = {Retail},  RetGrpCalendarYearMonth = {"$(#vMaxYearMonth)"}-1>} NettSalesQty)/count(DISTINCT {<  SalesChannel = {Retail}, RetGrpCalendarYearMonth = {"$(#vMaxYearMonth)"}-1>}IssueDate)

Regards

ASHFAQ

[#6]

vPreviousMonth = date(addmonths(MAX(Monthend1),-1),'YYYY-MM')   

[#7]

Hi Ashfaq

This returns the same values as the current month, also an underline error apears in the calc under the curly bracket  after the first -1>.

Magen

[tresesco]

It's because of max() in the variable declaration. If you want this vPreviousMonth variable not get affected by other filters like you wrote in main expression you have to define similarly like:

vPreviousMonth = date(addmonths(MAX(  {  < SalesChannel = {'Retail'}>} Monthend1),-1),'YYYY-MM')   

[#9]

Hi Tresesco

That helps, but now when ever i select the month filter it returns nulls.

If i select other filters it work fine.

Any ideas?

Regards,

Magen

[#10]

By the way the format of Monthend1 is 'YYYY/MM/DD' - if it makes a difference