topic Re: Filtering on a dimension when data model has uniqueness in QlikView

Filtering on a dimension when data model has uniqueness

Wed, 07 Oct 2015 08:17:36 GMT

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

Re: Filtering on a dimension when data model has uniqueness

r3iuk — Wed, 07 Oct 2015 09:19:08 GMT

How about:

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

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

Re: Filtering on a dimension when data model has uniqueness

Wed, 07 Oct 2015 09:46:02 GMT

The calculations are already done in hadoop.

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

Re: Filtering on a dimension when data model has uniqueness

SreeniJD — Wed, 07 Oct 2015 09:54:08 GMT

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

Re: Filtering on a dimension when data model has uniqueness

Wed, 07 Oct 2015 09:55:22 GMT

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

Re: Filtering on a dimension when data model has uniqueness

SreeniJD — Wed, 07 Oct 2015 10:01:09 GMT

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

Re: Filtering on a dimension when data model has uniqueness

Digvijay_Singh — Wed, 07 Oct 2015 10:06:22 GMT

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.

Re: Filtering on a dimension when data model has uniqueness

Thu, 08 Oct 2015 07:36:11 GMT

Any news?

Re: Filtering on a dimension when data model has uniqueness

Thu, 08 Oct 2015 07:38:25 GMT

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

Re: Filtering on a dimension when data model has uniqueness

Digvijay_Singh — Thu, 08 Oct 2015 08:27:13 GMT

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

Re: Filtering on a dimension when data model has uniqueness

Thu, 08 Oct 2015 13:14:54 GMT

Can you please explain what you did here?

could you please add the same solution if the date is also a dimension?

and how Can I do it for the large amount of dimension meta data that I have?

Re: Filtering on a dimension when data model has uniqueness

Digvijay_Singh — Thu, 08 Oct 2015 14:01:33 GMT

Hi,

The idea is to display any dimension when -

1. All Other dimensions are not selected ( Initial condition with no selection, this will display all dimension initially)

2. Show Dimensions When value in this dimension is selected, hide it when other dimensions are selected and this one is not selected.

So we will have (Dim1 AND Dim2 AND Dim3) OR Dim4 kind of condition in each 'enable condition' expression of each dimension. The AND section to check other dimensions are not selected and after OR to check if the dimension where we are putting this condition is selected.

PFA the attached updated file with Date also shown/hidden based on its selection properties.

Re: Filtering on a dimension when data model has uniqueness

Digvijay_Singh — Thu, 08 Oct 2015 14:04:05 GMT

I think this solution is not based on individual values of dimensions, the condition will expand or shrink based on # of dimensions we have.

Is this you wanted? Let me know if any improvement is needed?

Re: Filtering on a dimension when data model has uniqueness

Mon, 12 Oct 2015 06:42:28 GMT

try loading this csv.

I added 2 rows where I have only the a date.

in our aggregations when only a date is select it should show only this row, not the sum on all the rows who have this date.

also when I select the a certain date and a certain dimension it should only show me the relevant row.

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
February			20	17
January			24	16

Re: Filtering on a dimension when data model has uniqueness

Digvijay_Singh — Mon, 12 Oct 2015 07:59:41 GMT

I understand from this is that you always want to consider Date, Dim1, Dim2 to group your count and count unique. Since the conditions were put in the dimension - enable condition attribute, it was ignoring the not selected dimension and grouping measures based on selected dimensions. Now I have moved all the conditions to show/hide condition in presentation tab. See if this behaviour is close to your requirement or not.

Re: Filtering on a dimension when data model has uniqueness

Mon, 12 Oct 2015 10:26:47 GMT

almost, but I need the measure from the row that I have only the month in it? how do I narrow it to this point?

Re: Filtering on a dimension when data model has uniqueness

Digvijay_Singh — Mon, 12 Oct 2015 13:20:32 GMT

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