topic Re: Total amount with Set Analysis for last year data in QlikView

Total amount with Set Analysis for last year data

Wed, 21 Oct 2015 14:32:07 GMT

Hi,

I am using following set analysis to get last year distinct count of products.

Here, MTYear is a variable that calculates last year "Month_Year" correctly.

=count(DISTINCT{$<MTYearDt={$(MTYear)}>} Product_Nbr)

Above formula works perfectly in my table chart.

But, now i need % of total for each line in new column and my below formula doesn't give me right result.

=count(DISTINCT TOTAL{$<MTYearDt={$(MTYear)}>} Product_Nbr)

Appreciate your help and time on this..

Thx,

Sambha

Re: Total amount with Set Analysis for last year data

sunny_talwar — Wed, 21 Oct 2015 15:38:34 GMT

Can you share a sample with expected output? Your syntax doesn't look wrong to me.

Re: Total amount with Set Analysis for last year data

Wed, 21 Oct 2015 17:19:07 GMT

As you can see in the attached sample;

in Summary chart table:

Total LY Amount should be 250 instead of 1090 coming up. Current logic is including all amounts from last year irrespective of Group2 in current year.

Meaning that, for current year, we have only 2 values for Group2: (250 & 450), and for LYearAmount we get total 250 which is correct, but if we need total for last year as a separate column, I get wrong amount..

Thanks!

Re: Total amount with Set Analysis for last year data

sunny_talwar — Wed, 21 Oct 2015 17:25:10 GMT

See if this helps:

=If([Grouping BY] = 'Group1', sum(TOTAL <Group2>{$<Year={$(LYear)}>} Amount), sum(TOTAL <Group1>{$<Year={$(LYear)}>} Amount))

Re: Total amount with Set Analysis for last year data

Wed, 21 Oct 2015 18:27:39 GMT

Superb.. Thank you very much for this script; Can you please also tell me how does it work? I didn't get following part in this set analysis...

TOTAL <Group2>{

TOTAL <Group1>{

Thanks again!

-Sambha