Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Tricky Set Analysis Scenario

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.

11 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

So why the

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

is not the solution?

Not applicable
Author

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.

Not applicable
Author

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.

Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Thanks Again!

Not applicable
Author

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.