Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with aggregating an expression

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..

Capture.PNG

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

Capture.PNG

Can anyone see what i am doing wrong?

thanks, example attached

1 Solution

Accepted Solutions
sunny_talwar

May be this

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


Capture.PNG

View solution in original post

10 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Can anyone help with this?

sunny_talwar

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...

Capture.PNG

But drilled down now matches with non drilled down version

Capture.PNG

This isn't right?

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?

sunny_talwar

How did you come up with this number?

681,308.43

sunny_talwar

May be this

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


Capture.PNG

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Sorry! it should be this..

689,053.47

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

sunny_talwar

Sounds like a plan

Best,

Sunny

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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..