Sign InHelp

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can get started with Qlik Sense.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- New to Qlik Sense
- :
- Sum of one column based on three columns for bar s...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

01-21-2016
02:08 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

185 Views

11 Replies

01-21-2016
12:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

stalwar1

MVP

01-21-2016
12:30 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

01-21-2016
12:31 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

stalwar1

MVP

01-21-2016
12:32 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

dominicmander

Contributor

01-21-2016
12:35 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

01-21-2016
12:36 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Darn, misread it again

Not applicable

01-26-2016
04:15 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

01-26-2016
04:20 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

01-26-2016
01:26 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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