Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
So why the
=Sum({<Year=,Month=>} Amount)
is not the solution?
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.
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.
See attached qvw.
Thanks lucian and Gysbert. Both are good solutions, Gysbert's was easier to implement so that is what I used.
Thanks Again!
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.