topic Show sum total on all rows in QlikView

Show sum total on all rows

davyqliks — Sat, 16 Nov 2024 00:17:01 GMT

Hi,

I have the following expression with Set analysis which is working well and giving the individual values required:

Sum({$<Location = {'UKInv'},[Retailer Name] = {'Brand'}>}[Stock In House])

I now need a column next to this which required the the total of the above.

In the below simple example the above return 2,8,2 and i need an expression to give the total= 14 on each line

e.g.

Above SUM TOTAL SUM
2 14
8 14
2 14

Please can someone assist in making an expression to give me the total from this:

Sum({$<Location = {'UKInv'},[Retailer Name] = {'Brand'}>}[Stock In House])

Thank you in advance.

Daniel

lironbaram — Wed, 22 Jul 2020 11:11:10 GMT

have you tried

Sum({$<Location = {'UKInv'},[Retailer Name] = {'Brand'}>}total [Stock In House])

davyqliks — Wed, 22 Jul 2020 11:23:36 GMT

Thank you so much for this and your quick response. This has worked perfectly.

Thanks, i knew i had to get a total in there but wasted so much time trying to get the positioning right with no error.

Much Appreciated...Stay safe and Thanks again.

Daniel

davyqliks — Wed, 22 Jul 2020 14:26:52 GMT

Hi again,

I wonder if you could help again.

When using the given expression:

Sum({$<Location = {'UKInv'},[Retailer Name] = {'Brand'}>}total [Stock In House])

i also need to 'filter'/split the total by 2 dimensions

Item Code and Size Table.

This is so that i get the total sum of the stock in house by location and retailer name, But, a total for each change in item code and Size Table.

The expression above works great when i make those 2 filters however there are too many to fix like [Item Code] = {''item name} etc

Is there a way to filter the total in the given expression using those 2 dimensions?

so i get like below;

Item code Size Table qty total

A A B 2 14
A AB 10 14
A AB 2 14
b bb 2 10
b bb 8 10

Thanks Again @lironbaram

lironbaram — Wed, 22 Jul 2020 14:49:16 GMT

hi this should work

Sum({$<Location = {'UKInv'},[Retailer Name] = {'Brand'}>}total<[Item Code],[Size Table]> [Stock In House])

davyqliks — Wed, 22 Jul 2020 14:59:37 GMT

SERIOUSLY!!

As easy as that, the more i think i know the less i do.

You have massively helped me out today @lironbaram ... i cannot thank you enough!

Thanks so much, enjoy the rest of your day.