topic Strange Issue on Calculation Dimension in QlikView

Strange Issue on Calculation Dimension

Thu, 19 May 2011 01:58:22 GMT

Hi All,

I run into the following issue:

The following is my data table

load * Inline

[

Category,MemberId

A,1

A,2

A,3

B,1

B,2

]

I use pivot table to show the them, it looks like this:

Category, Class, Member count, total Member count based on Category

A, 3, 2, 2

A, 1, 1, 1

B, 1, 2, 2

There are two dimensions: Category and Class, Class is a calculation dimension: that is to class sales quantity group: like 1,2,3...

In this sample: there 2 guys(MemberId 1 and 2) have bought 3 products on A.

As for measure, Member count is count(distinct MemberId)

Now my question is on total Member count based on Category, it should be the total member based on category, for category, it shoule be 3, I use the expression : count( distinct TOTAL<Category> MemberId). But as you have seen, it is 2 and 1 for Category.

How to solve it?

If I did not make myself clear, please let me know.

Thanks.

Re: Strange Issue on Calculation Dimension

IAMDV — Thu, 19 May 2011 08:22:41 GMT

Please can you upload a QV document with sample data? Also demonstrating the issue in more detail...

Many thanks - DV

Re: Strange Issue on Calculation Dimension

Thu, 19 May 2011 09:42:29 GMT

Please refer to the attached.

Re: Strange Issue on Calculation Dimension

IAMDV — Thu, 19 May 2011 15:33:07 GMT

Hi,

I would generally use Set Analysis for this kind of issues (especially for pivot table). The expression you are using is actually counting the distinct values for each dimension. However, we wanted the total distinct count for all the data... isn't it?

In this scenario, I would use Dollar Sign Expansion with a Variable. Create a variable with the same syntax you had provided...

vCount =count( distinct TOTAL<Category> MemberId)

*Please note: It is very important to include "=" symbol at the beginning, while creating the variable. If you don't include "=" then QV expands the variable and evaluates it for each dimension. Which we don't want... so adding the "=" at the beginning makes the difference. And now QV evaluates the variable only once and it fetches the value instead of evaluating the variable.

Then I would use this variable in the pivot table and it shows 3 as total distinct count instead of counting for each dimension. Using dollar sign expansion to trick QV

Please use this expression in the pivot table:

$(vCount)

I am also attaching the example for your reference.

Please let me know if you need further help!

Cheers - DV

Re: Strange Issue on Calculation Dimension

Fri, 20 May 2011 01:40:00 GMT

Thanks for you reply.

For Category A, the total members is 3.

But for B, the total shoule be 2 not 3.

How to handle it?

Strange Issue on Calculation Dimension

qliksus — Fri, 20 May 2011 11:10:45 GMT

Hi try this expression and let me know its working or not

rangesum(top((Aggr(count(distinct MemberId),Category)),1,count(distinct total MemberId)))