topic Re: aggr() doesn't give correct data in QlikView

aggr() doesn't give correct data

qvqfqlik — Thu, 26 Jan 2017 16:28:55 GMT

I need some help with Aggr() not working in the expression. Can you please help me with this.

In the promoresultstesting tab

Before using aggr() in the expression for Customer count the data seems to be this. Requirement is to aggregate for the same Offer number

After using aggr() below is how it is happening, total should be 19, but its aggregating into single row

I have attached the file to this. Please help me with this.

Re: aggr() doesn't give correct data

petter — Thu, 26 Jan 2017 16:32:18 GMT

You probably pasted the images into your question... they have to be stored and then uploaded with the camera-button... so the two screenshots are broken for now...

Re: aggr() doesn't give correct data

sunny_talwar — Thu, 26 Jan 2017 16:40:43 GMT

You will get an idea of what OP wants from this discussion

Help with Aggr () not working

I don't have any issues with the total, but when OP opens the same file, they see 0 instead of 19

Re: aggr() doesn't give correct data

qvqfqlik — Thu, 26 Jan 2017 17:10:46 GMT

I uploaded screenshots to this initial post

Re: aggr() doesn't give correct data

qvqfqlik — Thu, 26 Jan 2017 19:23:49 GMT

I uploaded screenshots to this post. Can you please help me with this.

Re: aggr() doesn't give correct data

qvqfqlik — Fri, 27 Jan 2017 17:11:54 GMT

can you please help on this.

Re: aggr() doesn't give correct data

rubenmarin — Fri, 27 Jan 2017 18:24:28 GMT

Hi, I don't see the issue, wich line you say is aggregating into one line? what is the expected result?

Re: aggr() doesn't give correct data

rubenmarin — Fri, 27 Jan 2017 18:54:41 GMT

I think I'm getting now... for what I see there are options where it shows ok in rows and other in totals, so I used a simple If based on dimensionality() to pick each expression.

If this is what you are looking for, credits should go to Sunny, I just copied his work.

Re: aggr() doesn't give correct data

qvqfqlik — Wed, 01 Feb 2017 19:56:04 GMT

For some reason, when I open the same work into my computer, I see the last total row as 0

Re: aggr() doesn't give correct data

rubenmarin — Wed, 01 Feb 2017 20:43:20 GMT

I see '19'. Maybe is because of different version? I'm using QV 12.0 SR5

Re: aggr() doesn't give correct data

swuehl — Wed, 01 Feb 2017 22:07:38 GMT

Looks like 11.20 SR6, build 11.20 12347 is in use.

Maybe time to at least try a current 11.20 release?

Have you also tried to use a pivot table chart instead of the straight table? Maybe the dimension limits total is havin some issues in your software release.

Besides this, are you really sure you want to show 19? As far as I see, there are only 18 distinct customers involved.

Re: aggr() doesn't give correct data

qvqfqlik — Thu, 02 Feb 2017 00:16:47 GMT

Iam using 11.20.12347 SR6

Yes Stefan, I need to show it 19.

In my first post pictures, the same offernumber has two records , so after aggregating the total would be 19.

I have three different logics in the expressisons.

The last two logics are the ones for the pictures in my initial post.

Re: aggr() doesn't give correct data

swuehl — Thu, 02 Feb 2017 08:35:25 GMT

It's up to you and your requirements how you need to show the data.

I am just saying, that if you look at your record set, there are 18 distinct customers, not 19.

If you would use the table with the two detailed table lines, I can see why you want to see 18 +1 as total, but if you already group the records into one detailed line?

Well, as said, this is up to you.

Have you considered using a more recent software version or have you tried my sample with the pivot?

I think we can't really help you more, because the suggested solutions seem to work in recent software versions.

Re: aggr() doesn't give correct data

rubenmarin — Thu, 02 Feb 2017 08:44:24 GMT

There is also the option of changing the label to 'Offer Count' (or similar) to keep saying the truth.

Justify '19' because of counting the non-existant customer seems forced.