Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: Tricky Set Analysis Scenario

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

Re: Tricky Set Analysis Scenario

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

Re: Tricky Set Analysis Scenario

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

Re: Tricky Set Analysis Scenario

So why the

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

is not the solution?

Not applicable

Re: Tricky Set Analysis Scenario

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

Re: Tricky Set Analysis Scenario

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

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Tricky Set Analysis Scenario

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

Thanks Again!

Not applicable

Re: Tricky Set Analysis Scenario

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.

Community Browser