topic Re: Avoid Aggregation in Pivot table in QlikView

Avoid Aggregation in Pivot table

shah_jigesh16 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

We are working on a solution where

1. Using Pivot table with 3 Dimension

State

Status

Transaction

2. Expression: Counting the number of open Transaction based on date selection on UI. i.e. Based on selected date find the latest record for each transaction and verify for Open and Close and only report open as on that date.

The aggregation should be on Country level (i.e. Highest level).

The expression used gives a proper output as expected but when a state is selected output is aggregated based on State.

Please let me know on how can we avoid aggregating on State which is a dimension in Pivot Table when a state is selected.

Expected Output:

Without Selection of State Say "ABC" state show a number as 100.

But when State "ABC" is selected number changes to 120. Expectation is to show 100 even if ABC is selected.

Pivot table should also have only one line item if a state ABC is selected.

Thanks,

Jigesh

Re: Avoid Aggregation in Pivot table

shah_jigesh16 — Wed, 02 Aug 2017 18:32:31 GMT

Hi All,

Any suggestions on the issue.

Thank you once again.

Jigesh

Re: Avoid Aggregation in Pivot table

sunny_talwar — Wed, 02 Aug 2017 19:51:09 GMT

Not sure what your expression is, but lets say it looks like this Count(Measure), try this

Count({<State>} Measure) * Avg(1)

If you have some set analysis in Count like this

Count({<Field1 = {'XYZ'}>}Measure)

Then add the same one to the Avg(1) also, like this

Count({<Field1 = {'XYZ'}, State>}Measure) * Avg(1)

Re: Avoid Aggregation in Pivot table

shah_jigesh16 — Fri, 11 Aug 2017 16:17:30 GMT

Hi Sunny,

Sorry for late reply.

Using it dint really work.

Issue Detail Desc:

Explanation:

A pivot table should show a count of an item which is in open status. Here a particular item will exists in two or more states. In one state, the item will be in Open status and in other state, the same item will be in closed status. We need to take the latest record of each item and check for the status regardless of State, if it is Open then it is countable. So the count should be based on item not on the State.

To be clear, if I select a state(Dimension), the item should be countable only if it is the latest record and in Open status.

Even if the item is in Open status and not the latest record, it should not be a countable one.

For this I have used the below logic to neglect the State(Dimension) . By doing this, Pivot table is showing the value for all the states. But the Pivot table should show only the dimension which I have selected.

Dimension: State

Expression: =sum({<State=>} aggr(if(...., 1, 0), Item_ID))

Here, I have a selected Tamilnadu as a State.

Current Result:

State	Count
Tamilnadu	10
Kerala	15
A.P	5
Karnataka	20

Expected Result:

State	Count
Tamilnadu	10

Re: Avoid Aggregation in Pivot table

sunny_talwar — Fri, 11 Aug 2017 16:19:59 GMT

Would you be able to share a sample?