topic Re: Aggr function in dimensionless bar chart in QlikView

Aggr function in dimensionless bar chart

Anonymous — Wed, 30 Sep 2015 15:53:16 GMT

Hello QlikView experts,

I am trying to create a bar chart with expression total and for that I am using Aggr function. Below is my code :-

SUM(

AGGR(

(SUM([Price] *

sum({$ <Year = {"$(=(max(Year)-1))"},

Month = {"$(=(month(today())))"}>}

#Units)

)

) , ITM_ID) )

Here I am multiplying Price by Units at the ITM_ID level and want to sum this on Location_ID Level. Location can be added as a dimension to the bar chart. I tried putting Location_ID in above expression but that did not help either.

For some reason it is giving me 0 in text object and is not showing up in the bar chart.

Would like to know any obvious suggestions/error just looking at the above expression?

Thanks

Re: Aggr function in dimensionless bar chart

avinashelite — Wed, 30 Sep 2015 15:54:34 GMT

AGGR function will work only on the dimension ...you need to have dimension without that it won't work

Re: Aggr function in dimensionless bar chart

ramoncova06 — Wed, 30 Sep 2015 16:00:06 GMT

is there a specific reason why you are not multiplying price by units ?

you could use total

sum(total <[Location_ID>{$ <Year = {"$(=(max(Year)-1))"},

Month = {"$(=(month(today())))"}>}

#Units*[Price])

Re: Aggr function in dimensionless bar chart

Anonymous — Wed, 30 Sep 2015 16:00:41 GMT

Hi Avinash, thanks for quick reply. I have added dimension in bar chart to test my expression. It does not come up with value still! In addition to this, I also tried to add the dimension in aggr expression, see below. No luck!

SUM(

AGGR(

(SUM([Price] *

sum({$ <Year = {"$(=(max(Year)-1))"},

Month = {"$(=(month(today())))"}>}

#Units)

)

) , ITM_ID,LOC_ID) )

What am I missing?

Re: Aggr function in dimensionless bar chart

Anonymous — Wed, 30 Sep 2015 16:16:38 GMT

Hi Ramon, your expression is giving me some value so that is good and thanks for that! However it is different from what I am after. I am looking for sum product. Each unit value multiplied by price for that item will give me a value and I want to sum them up (for entire location.)

I have attached the spreadsheet to illustrate my requirement. Hope it is clear.

Re: Aggr function in dimensionless bar chart

ramoncova06 — Wed, 30 Sep 2015 17:09:50 GMT

ok, so I assume price for the itm_id can be more than once

you could try by using two aggr

aggr(

SUM(

AGGR(

(SUM([Price] *

sum({$ <Year = {"$(=(max(Year)-1))"},

Month = {"$(=(month(today())))"}>}

#Units)

)

), ITM_ID)),LOC_ID)

or by using the total outside of the aggr

SUM(total <LOC_ID>

AGGR(

(SUM([Price] *

sum({$ <Year = {"$(=(max(Year)-1))"},

Month = {"$(=(month(today())))"}>}

#Units)

)

), ITM_ID))

Re: Aggr function in dimensionless bar chart

pamaxeed — Wed, 30 Sep 2015 17:24:47 GMT

Hi,

look at the attached sample.

Is this what you are looking for?

Patric

Re: Aggr function in dimensionless bar chart

Digvijay_Singh — Wed, 30 Sep 2015 17:37:06 GMT

I think direct Unit and Price multiplication should work if you take Location as Dimension in Bar chart. See the attached based on your excel sheet. Perhaps it cannot be that simple, may be missing the point you want to explain

Re: Aggr function in dimensionless bar chart

Anonymous — Wed, 30 Sep 2015 20:34:34 GMT

Thanks for putting up a quick QVW Patric! Appreciate it. I see this is the same expression that Ramon shared and this is working for me as of now.

Re: Aggr function in dimensionless bar chart

Anonymous — Wed, 30 Sep 2015 20:38:14 GMT

Thanks Digvijay. Yes you got it right. Just that in my case the units will actually be an aggregate of each item id (because there can be more than one item id in the table). I have used expression shared by Ramon.