How to create customised total in pivot table

[Lucky1]

Hi

I have a field status

Status.                Id

Completed.       1

Pending.             1

New.                     2

New.                    2

I want to consider only new and completed status.but i want to create one more row to show all status count.my output should be

My o/p should be

Status.  Count(id)

All status    4

Completed 1

New.               2

How to add this all status

[stevedark]

Hi @Lucky1 

Unless I am missing something you should just be able to turn on totals for the dimension and get the total value as well?

If you need to have more control over things with grouping, you can create a new dimension that links all dimension values to themselves and also to the grouped value, like this:

Status	Status With All
Completed	Completed
New	New
Pending	Pending
Completed	All Status
New	All Status
Pending	All Status

You can create this table using this code:

StatusWithAll:
LOAD DISTINCT
    Status,
    Status as [Status With All]
RESIDENT YourExistingTable;

LOAD DISTINCT
    Status,
    'All Status' as [Status With All]
RESIDENT YourExistingTable;

Then you simply use the Status With All dimension in your pivot, rather than the Status dimension.

I've written a blog post which goes into this in more details and also describes how a similar approach can be used for accumulation as well:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Hope that helps,

Steve

[Lucky1]

Hi Steve,

Thanks for reply.In the dimension values i have to consider only new and completed i don't want pending status.but in total all i have to show along with pending status.

[stevedark · Accepted solution]

Hi @Lucky1 

There are two ways you can deal with excluding Pending as it's own row.

The first is to not load it into the Status With All dimension, you can do that with a WHERE statement:

StatusWithAll:
LOAD DISTINCT
    Status,
    Status as [Status With All]
RESIDENT YourExistingTable
WHERE Status <> 'Pending';

LOAD DISTINCT
    Status,
    'All Status' as [Status With All]
RESIDENT YourExistingTable;

Or you can do it in the expressions in the Pivot, this gives the advantage that you can have it included in some tables and not others in the same application:

sum({<[Status With All]-={'Pending'}>}Value)

Which approach is the best depends entirely on your use case.

Steve