Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Sum of one column based on three columns for bar sheet

Hi all,

     I have a problem in getting sum of one column which can be loaded in bar sheet depending on other columns...

For example check the following data:

orderno.hold reasonpricing elemnettotal cost
co1h1

mat

55.5
co1h1res65.5
co1h1fp5434.45
co1h2mat55.5
co1h2res65.5
co1h2fp5434.45
co2h1mat535
co2h1res853
co2h1fp3287

Now i have given pricing elemnt in X-axis and total cost to be shown  in y axis as per following condition

MY REQUIRMENT  is for each order no. i want cost for one pricing element  to be taken only once when ur summing it....means mat cost in bar sheet should be 55.5+535...it should not consider the other mat which has holdcode h2.

Output should be mat -->55.5+535  res --> 65.5+853 and fp should be 5434.45 + 3287..


I have tried quite alot of ways but couldnt succed...so please help me to solve this,....

11 Replies

Re: Sum of one column based on three columns for bar sheet

Maybe like this: sum({<[hold reason]-={'h2'}>}TOTAL <[hold reason]> [total cost])

MVP
MVP

Re: Sum of one column based on three columns for bar sheet

Correct me if I am wrong Gysbery, but I think for a chart with pricing element as dimension the expression would be this:

Sum({<[hold reason]-={'h2'}>} [total cost])


Capture.PNG

UPDATE: I guess we can leave both the expressions for him to see what he needs

Re: Sum of one column based on three columns for bar sheet

You're absolutely right. I didn't read the requirements correctly.

MVP
MVP

Re: Sum of one column based on three columns for bar sheet

No I think you are right. He wants to see For each Order no..

dominicmander
Contributor

Re: Sum of one column based on three columns for bar sheet

You may also try ...

Dimension: [pricing element]

Expression: sum(aggr(only([total cost]),[pricing element],[order no]))

aggr.PNG

... what this is doing is aggregating out the dimension of hold reason, before summing up pricing element. This should always work regardless of what other values may appear in the hold reason column in the future.

Re: Sum of one column based on three columns for bar sheet

Darn, misread it again

Not applicable

Re: Sum of one column based on three columns for bar sheet

Hi sunny and Gysbert,

     This holds good for if there are only two hold codes...and i guess u removed holdcode H2 from caluculation... what if there are multiple codes for one customer order...

like co1 can have h1 ,h2 and more...co2 may not have h1 and h2...it may have some other hold codes ...it has to take sum of total cost for each pricing element once for each Customer order and sum it up and show in bar sheet...

Not applicable

Re: Sum of one column based on three columns for bar sheet

Hi Dominic,

     It worked perfectly.Thanks a lot for the expression...

one doubt I have is.. 'only' function removes the other hold codes from the summation . is that what you mean?

dominicmander
Contributor

Re: Sum of one column based on three columns for bar sheet

What only() does is return the value if there is only one distinct value ... so in this case it looks at all the values in [total cost] for a given [order no] and [pricing element], and if it finds only one distinct value (which it does in the case of all your examples) then it returns that value.

I am assuming in your data that each combination of [order no] and [princing element] will always have the same [total cost] regardless of [hold reason].

The advantage of the approach I have suggested is that it doesn't matter what the hold reasons are ... if in the future you get a hold reason h3 or h4 in your data, or if one order does not have a hold reason h1 but only h2, then the expression will still work (continuing to assume that it is true that each combination of [order no] and [princing element] will always have the same [total cost] regardless of [hold reason]).