topic Re: if-statement expression not aggregating correctly in QlikView

if-statement expression not aggregating correctly

rothtd — Fri, 12 Oct 2012 13:53:10 GMT

Can someone help me with a percentage calculation that is not aggregating correctly? My expressions are using if-statements to filter out rows in a chart accordingly. [DPC %] is calculating correctly for each individual line in the chart, but the total for the expression is wrong. The total for [DPC %] is including rows in the chart are filtered out, so it is ignoring the if-statement. I believe this is because my expressions use sum(x) inside the if-statement – but I don’t know how else to do this expression. Any ideas?

Expressions:

Net Sales:

=if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

sum([Net Sales]))

DPC $

=if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

sum([DPC]))

DPC %

if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

sum([DPC]))

if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

sum([Net Sales]))

Re: if-statement expression not aggregating correctly

Fri, 12 Oct 2012 14:16:43 GMT

Try this.

Re: if-statement expression not aggregating correctly

Fri, 12 Oct 2012 15:11:13 GMT

Using set analysis, it shouldn't matter how many records you have. What part is not working?

Re: if-statement expression not aggregating correctly

rothtd — Fri, 12 Oct 2012 15:22:23 GMT

If you think through how the expression total is evaluated, it first looks at the if-statement/s (which are true) then evaluates the sum(x) portion. At this point it would be a sum of all records, meaning the if-statement is negated. Thus, the rest of the chart works great - but the expression total is always a sum of all records regardless. This is why I think I need to embed the if logic within the sum statement, but I can quite figure out the best way of doing that.

Re: if-statement expression not aggregating correctly

rothtd — Fri, 12 Oct 2012 15:25:27 GMT

Thanks rebeccad - I think this works with the example data because of the small number of records. When the data scales up to millions of rows this solution does not work.

I think I need to put the aggregation outside the if statement (or use a set expression instead), but I can get it to work.

Re: if-statement expression not aggregating correctly

Fri, 12 Oct 2012 15:31:08 GMT

That was why I renamed your Net Sales column to Net Sales (added one extra space) and then used your column headers as the expression:

[DPC $]/[Net Sales ]

This way it will only evaluate based on the sums in the two columns. Did you try that? Sorry, I guess I should have made that more clear.

Re: if-statement expression not aggregating correctly

rothtd — Fri, 12 Oct 2012 16:32:43 GMT

That's a really good idea. I couldn't get it to work however. It is still calculating based on all rows vs. only the visible rows. I added a 2 on the column names instead of your space to make the rename more visible.

DPC% 2 expression:

=[DPC $2] / [Net Sales 2]

Re: if-statement expression not aggregating correctly

Fri, 12 Oct 2012 16:43:57 GMT

Can you upload a sample file with customer data scrambled out? I tried a test document with a ton of records and it was working.

Re: if-statement expression not aggregating correctly

rothtd — Fri, 12 Oct 2012 17:30:39 GMT

You'll notice on this version that when the filter is turned off it works fine, but when the filter is turned on it does not work.

Filter off:

the DPC%2 total should be 33% (1,135,207,375 / 3,462,945,698 = 0.3278)

Filter on:

the DPC%2 total should be 26% (282,682,342 / 1,077,512,423 = 0.262)

Re: if-statement expression not aggregating correctly

Fri, 12 Oct 2012 18:07:10 GMT

See if this works.

Re: if-statement expression not aggregating correctly

rothtd — Fri, 12 Oct 2012 18:20:45 GMT

That works! Thanks - that was exactly what I was looking for!