Count of Rows in an Ad-hoc table

[diwakarnahata]

Hi,

I have an ad-hoc table created by Selection of a Set of Dimensions and Metrics. For now, I have two groups of Dimensions and one group of Metrics picker in Metadata. I want to get the possible number of rows in the Caption of the table when a set of Dimensions and Metrics are selected.

For, e.g. In the attached qvw, When Year is selected, the Caption should say Max Rows = 3, when Year and Country is selected, the Caption should say Max Rows = 18 (i.e. 3 x 6), etc.

PFA the qvw for for your reference.

Let me know ow this could be achieved in QlikView?

Thanks,

Diwakar

[anbu1984]

Check this

[tresesco]

If you use selection style LED (single value selection for the dimensions, _Dimension and _Dimension, the following expression should work:

=Count(DISTINCT  $(=_Dimension)&$(=_Dimension))

[diwakarnahata]

Hi Anbu,

Thanks for the reply but putting an if condition for all the possible combinations of Dimensions would be difficult as these are lots of Dimensions plus there could be more Dimensions coming up, so this would lead to a lot of hard-coding. And also, the total Permutations would be huge..

I am working on the below expression (present in the Caption), where I was trying to get the count of Distinct dimensions selected from the list box. But it is taking that as String argument instead of column names.

=COUNT(DISTINCT GetFieldSelections(_Dimension, ' & ',20) )

Regards,

Diwakar

[diwakarnahata]

Hi Tresesco,

In this ad-hoc table, any number of Dimensions can be selected from any group of list boxes (Dimensions and GeoDimensions in this case), and the table caption should show the count rows it will contain as a result of selection of these set of columns.

For e.g. if ProductCategoryName, year and Country is selected then the count in the table Caption should show as 66 which is same as the expression COUNT(DISTINCT Year & ProductCategoryName & Country)

Let me know how this can be achieved?

Thanks and Regards,

Diwakar

[diwakarnahata]

Hi,

I got this to work as below, but..:

1. Created a variable v_GetFields=GetFieldSelections(_Dimension, ' & ',100)

2. Used this variable in the caption as =COUNT(DISTINCT $(v_GetFields) )

It works fine for one group of Dimensions. But when there are more than 1 groups of Dimension Selection still some more work is required. For that, I need to build a string into the above variable dynamically with Ampersands '&' so that the expression syntax doesn't break.

For e.g. I have two strings 'a & b & c' and 'x & y & z' coming from two groups, i need to build a string by concatenating the two strings dynamically (i.e. either of them can appear at a time) with an '&' between them only if both of them exists.

Let me know how this could be achieved.

Thanks,

Diwakar

[tresesco]

Add the other dimensions in variable definition, like:

v_GetFields=GetFieldSelections(_Dimension, ' & ',100)&' & '&GetFieldSelections(_Geo_Dims,' & ',100)

[diwakarnahata · Accepted solution]

Hi Tresesco,

Thanks for the reply.

It worked for me with the below expression:

=if(GetSelectedCount(_Dimension)>0,GetFieldSelections(_Dimension, ' & ',100),0)&' & '& if(GetSelectedCount(_Geo_Dims)>0,GetFieldSelections(_Geo_Dims,' & ',100),0)

But, I have one more level of complexity.

The user should not see the actual column names in the list boxes. Instead he should see alias names.

So, how can i take the actual column names from the selection of alias columns by the user.

For e.g. if user selects 'Prod Cat' from the List box of Dimension alias, he should see the Count as 4, which comes when the 'ProductCategoryName' is selected as of now.

How can i GetFieldSelections based on Selection in its alias (without using triggers) ?

Attached is the qvw for your reference.

Thanks,

Diwakar

[diwakarnahata]

Hi All,

Any idea how we can GetFieldSelections from the alias column as described above?

Regards,

Diwakar