Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 reason | pricing elemnet | total cost |
---|---|---|---|
co1 | h1 | mat | 55.5 |
co1 | h1 | res | 65.5 |
co1 | h1 | fp | 5434.45 |
co1 | h2 | mat | 55.5 |
co1 | h2 | res | 65.5 |
co1 | h2 | fp | 5434.45 |
co2 | h1 | mat | 535 |
co2 | h1 | res | 853 |
co2 | h1 | fp | 3287 |
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,....
Maybe like this: sum({<[hold reason]-={'h2'}>}TOTAL <[hold reason]> [total cost])
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])
UPDATE: I guess we can leave both the expressions for him to see what he needs
You're absolutely right. I didn't read the requirements correctly.
No I think you are right. He wants to see For each Order no..
You may also try ...
Dimension: [pricing element]
Expression: sum(aggr(only([total cost]),[pricing element],[order no]))
... 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.
Darn, misread it again
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...
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?
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]).