Solved: Filtering on a dimension when data model has uniqu... - Qlik Community

Report Inappropriate Content · ‎2015-10-07

Hi all,

I have the following data model:

Date	Dimension 1	Dimension 2	Count	Count unique
January	A		10	8
January	A	B	12	9
January		B	2	1
February	A		12	8
February	A	B	17	13
February		B	5	3

I have Filters in the model for the dimensions and an expression on sum (count) and sum (count unique).

the issue is, when I choose a filter only on dimension1 = A on January I want to get the values only from the row where I have only A, and exclude from the expression the row with both A and B.

how can I do that?

Thanks,

Boris

Digvijay_Singh · ‎2015-10-12

You are right, it is showing either extra records or grouped records. I am not finding any possibility of only setting up dimensions and achieving the requirement.

But I found the set expression condition which is generic for each expression.

Sum({<Date={'$(=GetFieldSelections(Date))'},[Dimension 1]={'$(=GetFieldSelections([Dimension 1]))'},[Dimension 2]={'$(=GetFieldSelections([Dimension 2]))'}>}Count)

I had to change nulls to '-' for proper comparison in the script.

Script added in preceding load as -

Load if(Len(Date)=0,'-',Date) as Date,

if(Len([Dimension 1])=0,'-',[Dimension 1]) as [Dimension 1],

if(Len([Dimension 2])=0,'-',[Dimension 2]) as [Dimension 2],

Count,

[Count unique];

But now I think its working as desired

View solution in original post

r3iuk · ‎2015-10-07

How about:

For the Count expression: ( {$-<Dimension2 = {'*'} >}Value)

For the Count Unique expression: ( {$-<Dimension2 = {'*'} >} DISTINCT Value)

Report Inappropriate Content · ‎2015-10-07

The calculations are already done in hadoop.

the question is how will the expression know which type of dimension combination I want to see.

SreeniJD · ‎2015-10-07

If I understand you correctly, you want to display data in Dimension1 when Dimension 2 is Null and vice versa..

something like.. if(isnull(dimension1),dimension2)

Regards,

Sreeni

Report Inappropriate Content · ‎2015-10-07

correct, but I will have around 6-7 dimensions, I can't write all this permutation in the expression.

SreeniJD · ‎2015-10-07

I have some rough idea.. which need to be developed..

each row will be identified by a recno() in QV... so for each of this recno() check count() of dimension which are having values.. lets say for recno()=1, if you have 6 dimensions, and you have only one dimension value corresponding to that recno() then that record can be displayed..

let me develop this and come back to you..

HTH

Sreeni

Digvijay_Singh · ‎2015-10-07

See if this can help -

Used - GetFieldSelections([Dimension 1],',')<>'A' in Dimension 2 enable condition. I know it is just a sample but similar thing can be tried on actual data.

Report Inappropriate Content · ‎2015-10-08

Any news?

Report Inappropriate Content · ‎2015-10-08

How? I have 200 possible values for Dimension 1 , 20 Possible for Dimension 2.

Digvijay_Singh · ‎2015-10-08

If you are ok with the results, the same can be produced by below, in fact handles the same selection for Dim2 as well.

Dimension 1 enable condition - len(GetFieldSelections([Dimension 2]))=0 OR len(GetFieldSelections([Dimension 1]))>=1

Dimension 2 enable condtion - len(GetFieldSelections([Dimension 1]))=0 OR len(GetFieldSelections([Dimension 2]))>=1

Filtering on a dimension when data model has uniqueness