topic Re: Wrong Aggregations in Set Analysis in QlikView

Wrong Aggregations in Set Analysis

alexandergrando — Fri, 30 Jun 2017 21:55:24 GMT

Hello,

I am trying to SUM units belonging to a property using set analysis. I tried using this expression but it is only showing the result on a single field value with the total being correct:

=SUM({<NPBegDateOfOperation ={"<$(=Date(vNPrintingDate, 'M/D/YYYY')) >=$(=Date(AddMonths(vNPrintingDate, -1), 'M/D/YYYY'))"}>} aggr(NPTotalUnitCount, NPPropertyKey))

Here is the screenshot of the incorrect expression results:

Can someone help me understand what is going on or how to fix this, please?

Re: Wrong Aggregations in Set Analysis

swuehl — Fri, 30 Jun 2017 22:12:35 GMT

Maybe a grain mismatch of your advanced aggregation dimension and your outer chart dimension?

Pitfalls of the Aggr function

Hard to tell with the very few context information provided.

Re: Wrong Aggregations in Set Analysis

alexandergrando — Fri, 30 Jun 2017 22:23:47 GMT

Hi Stefan,

Thanks for the reply. I'll give the article a careful read. The funny thing is that I get correct results when I use a slightly different expression (changed fields are in bold):

=SUM({<NPBegDateOfOperation ={"<$(=Date(vNPrintingDate, 'M/D/YYYY'))"}, NPEndDateOfOperation ={">=$(=Date(vNPrintingDate-1, 'M/D/YYYY'))"}>} aggr(NPTotalUnitCount, NPPropertyKey))

This leads me to believe that there is another way to write the incorrect expression so that it produces correct results. So that is what I am basically looking for.

Re: Wrong Aggregations in Set Analysis

vinieme12 — Sat, 01 Jul 2017 10:59:13 GMT

Please post a sample app , can't help much without looking at the app

Preparing examples for Upload - Reduction and Data Scrambling

Re: Wrong Aggregations in Set Analysis

alexandergrando — Wed, 05 Jul 2017 17:04:37 GMT

Thank you both for your answers. While trying to scramble my fields, I noticed that one of the columns was not scrambling. I suddenly realized that I was using duplicate columns with a different name from another table. That is why the numbers were acting funny.