topic Re: Set Analysis query in QlikView

Set Analysis query

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis query

sunny_talwar — Wed, 05 Oct 2016 20:02:53 GMT

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

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

Re: Set Analysis query

effinty2112 — Wed, 05 Oct 2016 21:39:01 GMT

Hi Jim,

This seems to work:

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

Cheers

Andrew

Re: Set Analysis query

rajpreeths — Thu, 06 Oct 2016 06:38:57 GMT

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

Re: Set Analysis query

Thu, 06 Oct 2016 08:38:56 GMT

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

Jim