Set Analysis query

[#1]

Hello

I’m new to Set Analysis, in the table I’m trying to calculate the total Sales for each Group but only if the Customer is selected. I would want the total Sales by the latest year.

Customer Group Sales Year Cust A group 1        25,000 2015 Cust B group 1        20,000 2015 Cust C group 1        35,000 2015 Cust A group 1        30,000 2014 Cust B group 1        20,000 2014 Cust D group 2        15,000 2014 Cust D group 2        40,000 2015 Cust E group 2        25,000 2015

Any help would be appreciated.

Thanks in anticipation

[sunny_talwar]

Not sure what the exact output needs to look like, but may be this:

Sum(TOTAL <Group> {<Customer, Group = p(Group)>}Sales)

[effinty2112 · Accepted solution]

Hi Jim,

This seems to work:

Sum({$<Customer=, Group = P({<Group>})>}Sales)

Cheers

Andrew

[rajpreeths]

Hi Jim,

You can try with this expression for obtaining the total sales by latest year.

Sum({<Group=P(Group),Year={$(=Max(Year))}>}Sales)

And if you want to show the data only if the customer is selected then you can try with calculated condition functionality available in the General tab of chart properties.

PFA the file for further reference.

Hope this helps 🙂

Br,

Rajpreeth S

[#5]

Thanks Andrew and Sunny T - both solutions work perfectly. Thanks again for responding so quickly, much appreciated.

Jim