topic Tricky Set Analysis Scenario in QlikView

Tricky Set Analysis Scenario

Tue, 11 Jun 2013 19:13:29 GMT

A few columns of my data set include Year, Month, AccountNumber, and Amount. Often an AccountNumber will show up for multiple Months and/or Years, but I only want to count each AccountNumber once each year.

I created one bar graph with "Year" as my X-axis and "Number of Accounts" as my Y-axis to show a year-over-year comparison of managed accounts. I used the following expression to give me the output I want:

Count({<Year=,Month=>} DISTINCT AccountNumber)

The Set Analysis for Year and Month are needed because those filters are set to "Always one selected value". For this graph, I want to ignore those 2 filters yet allow all others.

The problem I'm having now is how to create this same chart but using "Amount" as my Y-axis. I can't use a similar expression since the AccountNumber is the identifier I need, yet instead of counting those I want to sum the corresponding Amounts. I hope this makes sense.

Thanks in advance for any help you can provide.

Re: Tricky Set Analysis Scenario

Gysbert_Wassenaar — Tue, 11 Jun 2013 19:20:12 GMT

It sounds like you want the sum of amount per accountnumber disregarding selections in Year and Month. If that's correct you can use AccountNumber as dimension and sum({<Year=,Month->} Amount) as expression. If not, please explain in more detail what you need and prepare an example document.

Re: Tricky Set Analysis Scenario

Tue, 11 Jun 2013 19:31:30 GMT

I don't think that's the solution. I want the sum of amount per year, as such I'm using Year as the dimension. However, it's not just sum of Amount per year because any given AccountNumber could be listed for any number of Months each year, but I only want each AccountNumber's Amount included once in the total for each year. I'll work on an example.

Re: Tricky Set Analysis Scenario

Tue, 11 Jun 2013 19:46:00 GMT

Here is a very simple example. Now I want to create this same chart, but instead of Count of AccountNumber I want to Sum Amount. Hopefully this better illustrates my problem.

Re: Tricky Set Analysis Scenario

Tue, 11 Jun 2013 20:08:54 GMT

So why the

=Sum({<Year=,Month=>} Amount)

is not the solution?

Re: Tricky Set Analysis Scenario

Tue, 11 Jun 2013 20:18:02 GMT

Because then it would sum many accounts multiple times. Check out the raw data set for the Qlik file - based on your suggestion, AccountNumber "1" would be summed four times for 2009 - I only want to sum it once.

Total Amount for 2009 is 1,145, yet if only unique Accounts are included (each AccountNumber 1, 2, 3, 4, 5 added just once) the Total Amount would be 545. This last figure is what I want to appear in the graph.

Re: Tricky Set Analysis Scenario

Tue, 11 Jun 2013 21:48:38 GMT

1. Change "Month" data from text to integer (Jan=1, Feb = 2,..)

2. Add a flag to your table in the script:

LEFT JOIN (TRANSACTIONS)

LOAD Year, AccountNumber, max(Month) as Month, 1 as Flag

resident TRANSACTIONS

GROUP BY Year, AccountNumber;

3. Then, use:

=Sum({<Year=, Month=>} Amount * Flag)

in your chart.

Re: Tricky Set Analysis Scenario

Gysbert_Wassenaar — Wed, 12 Jun 2013 10:42:29 GMT

See attached qvw.

Re: Tricky Set Analysis Scenario

Wed, 12 Jun 2013 12:22:01 GMT

Thanks lucian and Gysbert. Both are good solutions, Gysbert's was easier to implement so that is what I used.

Thanks Again!

Re: Tricky Set Analysis Scenario

Wed, 12 Jun 2013 15:25:29 GMT

There's a small difference: my solution returns the latest value in year, Gysbert's solution returns the biggest value in year.

If your table is a "point-in-time" value list (as I believe it is), be aware of what you choose.

Re: Tricky Set Analysis Scenario

Wed, 12 Jun 2013 15:46:24 GMT

I actually ended up using a 3rd solution as Gysbert's wasn't working properly (the data in the chart was changing with the month/year selection which I need to avoid) and yours populated a 1 in every "flag" cell so that didn't work either.

I created a case statement in the SQL code I was using to create my data set that creates a new column, only pulling in the amount once per account.

My example wasn't very good, so unfortunately the solutions provided didn't work when I applied them to my actual file.

Thanks for all of the help though as these different view points did get me to the solution, even it was in a round-about way.

Cheers!

Re: Tricky Set Analysis Scenario

namrata_maheshw — Mon, 07 Jan 2019 14:49:24 GMT

we can achieve that directly in UI by using below expression-

SUM(AGGR(max(AMOUNT),ACCOUNTNUMBER))