Total amount with Set Analysis for last year data

[#1]

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

[sunny_talwar]

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

[#3]

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!

[sunny_talwar · Accepted solution]

See if this helps:

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

[#5]

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