topic Filters not working with Set Analysis in QlikView

Filters not working with Set Analysis

Fri, 20 Jun 2014 07:23:16 GMT

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

Re: Filters not working with Set Analysis

tresB — Fri, 20 Jun 2014 07:26:56 GMT

Remove '1' and try like:

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

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

Re: Filters not working with Set Analysis

Fri, 20 Jun 2014 07:52:26 GMT

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

Re: Filters not working with Set Analysis

tresB — Fri, 20 Jun 2014 08:05:42 GMT

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

Re: Filters not working with Set Analysis

ashfaq_haseeb — Fri, 20 Jun 2014 08:05:48 GMT

Hi try below

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

Regards

ASHFAQ

Re: Filters not working with Set Analysis

Fri, 20 Jun 2014 08:18:28 GMT

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

Re: Filters not working with Set Analysis

Fri, 20 Jun 2014 08:22:35 GMT

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

Re: Filters not working with Set Analysis

tresB — Fri, 20 Jun 2014 08:33:47 GMT

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

Re: Filters not working with Set Analysis

Fri, 20 Jun 2014 08:47:00 GMT

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

Re: Filters not working with Set Analysis

Fri, 20 Jun 2014 08:54:12 GMT

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