Strange Issue on Calculation Dimension

[#1]

Hi All,

I run into the following issue:

The following is my data table

load * Inline

[

Category,MemberId

A,1

A,1

A,1

A,2

A,2

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.

[IAMDV]

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

Many thanks - DV

[#3]

Please refer to the attached.

[IAMDV]

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

[#5]

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?

[qliksus · Accepted solution]

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

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