topic Re: Help with Aggr () not working in QlikView

Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 01:28:38 GMT

I am trying to aggregate the total value of the count result as below. but this is not happening

Sum(Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}>} DISTINCT BILLTOCUSTEDP),OFFERNUM))

I am trying to aggregate the same offernumber

below is before aggregating:

below is after aggregating, the total should be 19 , but it still shows as 18

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 02:13:50 GMT

What happens when you add Offer Name to your aggr?

Sum(Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}>} DISTINCT BILLTOCUSTEDP),OFFERNUM, [Offer Name]))

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 02:19:53 GMT

Below is when I include Offer name, its'nt still not aggregating

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 02:20:58 GMT

I am not sure what you mean? Do you not want to see the total as 19? What do you want to see?

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 02:29:18 GMT

Yes, when its aggregated, I want it to to be shown as 19 in a single row. Below , instead of 18 I want it to be 19

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 02:31:49 GMT

May be try this:

If(Len(Trim([Offer Name])) > 0, Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}>} DISTINCT BILLTOCUSTEDP),OFFERNUM, [Offer Name])))

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 03:11:47 GMT

Thanks Sunny , In that row , I got it as 19 , but in the Total row, I still see it as 0

Re: Help with Aggr () not working

Anil_Babu_Samineni — Wed, 25 Jan 2017 03:16:05 GMT

Would you provide application to work on it? We may help you what was the wrong over there?

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 03:17:29 GMT

May be make sure of Dimensionality() here.... Check what you get for Dimensionality() in the total row and use or condition like this:

If(Len(Trim([Offer Name])) > 0 or Dimensionality() = '---', Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}>} DISTINCT BILLTOCUSTEDP),OFFERNUM, [Offer Name])))

Replace '---' with the number you find when you use Dimensionality() as an expression

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 03:33:53 GMT

I guess you deleted your comment, but this might still be useful to see....

Did you read my comment?

If you are still not sure, take a look here:

How to use - Dimensionality()

If this doesn't help, I would suggest you to share your app so that we can help you out

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 03:35:41 GMT

If(Len(Trim([FULLOFFERNAME])) > 0 or Dimensionality() = '6', Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}

>} DISTINCT BILLTOCUSTEDP),OFFERNUM,FULLOFFERNAME)))

it is not aggregating as below

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 03:37:54 GMT

Do you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

You can try this:

If(Len(Trim([FULLOFFERNAME])) > 0 or Dimensionality() <= 6, Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}

>} DISTINCT BILLTOCUSTEDP),OFFERNUM,FULLOFFERNAME)))

If(Len(Trim([FULLOFFERNAME])) > 0 or Dimensionality() <= 5, Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED}

>} DISTINCT BILLTOCUSTEDP),OFFERNUM,FULLOFFERNAME)))

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 03:48:17 GMT

I have reduced the data and attached the app file to the original post.

Thanks a lot Sunny , I tried Dimensionalitiy() <=5 and also <=6 , but it didnot work.

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 03:51:30 GMT

This seems to be working, isn't it?

If(Len(Trim([FULLOFFERNAME])) > 0 or Dimensionality() <= 5, Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED},

ORDER_YEAR *= {">=$(vStartYear)"},

REPORTING_YEAR=,

REPORTING_MONTH =,

REPORTING_QUARTER=,

REPORTING_DAY=,

REPORTING_DATE=,

SHIP_YEAR=,

SHIP_MONTH=,

SHIP_QUARTER=,

SHIP_DAY=,

SHIPDATE=

>} DISTINCT BILLTOCUSTEDP),OFFERNUM,FULLOFFERNAME)))

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 04:03:22 GMT

Not sure, for some reason in my app its showing 0. please help me

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 04:07:05 GMT

‌Did you try to open the app I attached? you don't see 19 for total in that one also?

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 04:14:40 GMT

I opened the app you've attached, I think you have attached my original file. can you please post the correct one with your changes

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 04:28:53 GMT

Can you please attach your file changes.

I used the same , but still seeing 0

If(Len(Trim([FULLOFFERNAME])) > 0 or Dimensionality() <= 5, Sum(TOTAL <OFFERNUM>Aggr(Count({<RECORD_TYPE = {ORDERED, SHIPPED},

ORDER_YEAR *= {">=$(vStartYear)"},

REPORTING_YEAR=,

REPORTING_MONTH =,

REPORTING_QUARTER=,

REPORTING_DAY=,

REPORTING_DATE=,

SHIP_YEAR=,

SHIP_MONTH=,

SHIP_QUARTER=,

SHIP_DAY=,

SHIPDATE=

>} DISTINCT BILLTOCUSTEDP),OFFERNUM,FULLOFFERNAME)))

Re: Help with Aggr () not working

qvqfqlik — Wed, 25 Jan 2017 05:11:01 GMT

In your app file I see Dimensionality()='6', and its not aggregating, and the total is 0

Re: Help with Aggr () not working

sunny_talwar — Wed, 25 Jan 2017 09:40:08 GMT

Attaching again