topic Re: Help with aggregating an expression in QlikView

Help with aggregating an expression

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi All.

I have the following expression

sum(aggr($(vAnnualSalesv2),SubCat,SubCat1,[Item Description]))

which is made up of the following

$(vAvgPrice)*$(vForecastSales)+$(vTotalSalestoLastPeriod)

I thought it was working but noticed that the results were lower than expected. On investigation i found that some rows of data were not being included because they didn't have a 'ForecastSales' value (as they have been discontinued).

i have therefore changed the expression to the following..

if(isnull($(vForecastSales)),$(vTotalSalestoLastPeriod),sum(aggr($(vAnnualSales),SubCat,SubCat1,[Item Description])))

this populates a straight table correctly when i am drilling down to 'Product' level, but it doesnt aggregate properley when going back up the drill down.

as you can see, the total for the 3 lines add correctly..

but when going back up yoiu can see that it shows a different value (highlighted - its missing the 7,102.08)

Can anyone see what i am doing wrong?

thanks, example attached

Re: Help with aggregating an expression

hopkinsc — Tue, 01 May 2018 07:34:20 GMT

Can anyone help with this?

Re: Help with aggregating an expression

sunny_talwar — Tue, 01 May 2018 09:17:51 GMT

Are you sure that they drilled down version is the right number? I used this

sum(aggr(if(isnull($(vForecastSales)),$(vTotalSalestoLastPeriod),$(vAnnualSales)),SubCat,SubCat1,[Item Description]))

Not drilled down version looks almost the same, except one change...

But drilled down now matches with non drilled down version

This isn't right?

Re: Help with aggregating an expression

hopkinsc — Tue, 01 May 2018 11:58:28 GMT

Hi Sunny,

Thanks for your reply.

What i need is when drilled down, the top line should display 7102.08, which then changes the total to 134,248.32.

Then going back up a level i should see that total value for that particular subcat1 (Kbd Pxafxq: Uiibdl AT/Pzagix) is showing 681,308.43.

then obviously going up a higher level would also aggregate correctly.

does that make sense?

Re: Help with aggregating an expression

sunny_talwar — Tue, 01 May 2018 12:11:07 GMT

How did you come up with this number?

681,308.43

Re: Help with aggregating an expression

sunny_talwar — Tue, 01 May 2018 12:19:32 GMT

May be this

Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod), $(vAnnualSales)), SubCat, SubCat1, [Item Description]))

Re: Help with aggregating an expression

hopkinsc — Tue, 01 May 2018 12:21:41 GMT

Sorry! it should be this..

689,053.47

Re: Help with aggregating an expression

hopkinsc — Tue, 01 May 2018 12:23:25 GMT

I think thats it! it looks like one of the others was also wrong which is now correct too.

Thanks Sunny, i will put this into my live doc and see what happens

Re: Help with aggregating an expression

sunny_talwar — Tue, 01 May 2018 12:24:20 GMT

Sounds like a plan

Best,

Sunny

Re: Help with aggregating an expression

hopkinsc — Tue, 01 May 2018 12:55:01 GMT

OK that seems to work for that expression, are you able to help with getting the same thing to work on some other expressions?

I am just about to create a new discussion..

Re: Help with aggregating an expression

sunny_talwar — Tue, 01 May 2018 13:04:36 GMT

Sure