topic Re: Sense Pivot table - sum Amounts based on 5 customers in QlikView

Sense Pivot table - sum Amounts based on 5 customers

deerhunter — Sat, 23 Jul 2016 23:10:59 GMT

My data has 50 customers with financial sales revenue in the main fact table,

The requirement is that In this Pivot table, I only want to show Sum(Amounts) of 5 specific ones each in their own column.

How do I limit those customers listed in the pivot table and calculate each of the 5 customer revenue amounts?

Any help is appreciated...

Re: Sense Pivot table - sum Amounts based on 5 customers

Anil_Babu_Samineni — Sun, 24 Jul 2016 12:20:58 GMT

Like this,

Sum({<fieldname = {'customer1','customer 2'...}>}sales)

Re: Sense Pivot table - sum Amounts based on 5 customers

Gysbert_Wassenaar — Sun, 24 Jul 2016 13:21:18 GMT

Select those five customers first. Then the pivot table will show only those five customers. You could also save this selection in a bookmark and use the bookmark in a set analysis expression: sum( {MyBookMark} Amount)

Re: Sense Pivot table - sum Amounts based on 5 customers

deerhunter — Sun, 24 Jul 2016 13:40:06 GMT

Thank you, The user only wants to see 5 specific customers and not expose the rest to anyone. They also want a total column of those 5 along with a column for others not shown in the table.

Re: Sense Pivot table - sum Amounts based on 5 customers

Anil_Babu_Samineni — Sun, 24 Jul 2016 14:35:01 GMT

Buck, this situation might be my expression useful church that

Re: Sense Pivot table - sum Amounts based on 5 customers

Gysbert_Wassenaar — Sun, 24 Jul 2016 14:42:25 GMT

Perhaps a calculated dimension works best then:

=If(Match(Customer, 'Jim', 'Jon', 'Jack', 'Joe', 'Jesse'), Customer, 'Other')

Re: Sense Pivot table - sum Amounts based on 5 customers

deerhunter — Mon, 25 Jul 2016 21:11:55 GMT

Yes i can see how that would work to restrict names but where is the sum(sales) fit in that statement?

Thank you

Re: Sense Pivot table - sum Amounts based on 5 customers

Gysbert_Wassenaar — Tue, 26 Jul 2016 06:43:53 GMT

A calculated dimension is a dimension, not a chart expression. If you want to change the expression instead of using a calculated dimension (not a bad idea, recommended in fact) then try something like sum({<Customer={'Jim','Jon','Jack','Joe','Jesse'}>}sales). That is what loveisfail proposed above.