Dimensional Count Distinct

[jorgie]

hi to all and thank in advance for you time.

I have the following calculations

FieldA	FieldB	time		I need to
		count( distinct  [id] )	count (distinct total <[time]> [id])	????
ValueA		70	100	100
	TypeA	40	100	10
	TypeB	50	100	20
	TypeC	60	100	30
	TypeD	10	100	40
ValueB		100	100	100
	TypeA	10	100	10
	TypeB	20	100	20
	TypeC	30	100	30
	TypeD	40	100	40

I need to have the distinct total for any dimension I expand as in the example

Thank u!

[Kushal_Chawda]

@jorgie  How did you arrive to that values?  please share some dummy data with expected output

[jorgie]

thanx for replying.

As you can see the logic is on the column headers.

I created the data 

thanx again

[anat]

as per your sample data for Value A and Type A count is 40 but how you want to populate 10.

[jorgie]

when i use the Count( Distinct Total <[time]> [customers]) I have the total which is 100 for the dimension of time.

you are right, cant be 10 for 40.

I want in general to have the total of the Type dimension.

[Kushal_Chawda]

@jorgie  I still did not understand expected output.

[M_B]

If I understood your question:

Try fiddling around with 'Aggr(nodistinct Count(measure), FieldA, FieldB)' instead on the field(s) you want to group your sum by.

I had a similar issue with trying to produce percentages per each value for a dimension.

Hope this helps.

[jorgie]

you did understand it and thank you @M_B !

Unfortunattely partially solves it. I gets me the dimensional count when I expand the dimension. But not the total. If the first level dimension is collapsed I get null values. I tried to use 3 dimensions but then it all got null

I will try to simplify it and for the rest people who got in proccess to help (@Kushal_Chawda  and @anat ) which I also thank them.

• I have FieldA, FieldB in rows of a pivot, collapsed. I also have expanded the time dimension in columns.

1. If I use 
   Count(DISTINCT TOTAL  value) I will have the total of all data base for each time period and each dimension.
2. If I use 
   Count(DISTINCT TOTAL <time>  value) I will have for each column of time the total of the specific time period that is refered. The issue is that if I expand the row dimension I still get the same total across.

• I need for each dimension I expand to have the total that is refering to the dimension I am using as well.

Hope I got clarified now

Thank you all for your time

PS I will never manage to learn all these things about qlik. How this expression work really @M_B  ???

[Kushal_Chawda]

@jorgie  why do you need count(distinct total value). Why don't you just use Count(DISTINCT value) 

[Kushal_Chawda]

@jorgie  if you want just the total to be by other measure try below

=if(dimensionality()=1,
Count(DISTINCT TOTAL <time> value),
Count(DISTINCT value))