topic Re: Expression help in QlikView

Expression help

Sun, 05 Jan 2014 22:02:32 GMT

Hi,

I have a Location Dimension with the following hierarchy ( Starting from the lowest order) : Store, Field employees, Managers,Zone, Region. I have created a cyclic group with these fields names, in order, as " Retail". I have a straight table where I have this cyclic group as well as 3 expressions. The last expression defines whether a Store or a Field Employee or a Manager etc is "A","B" or "C" based on a number range.

I want to count the number of Zone with "A" or "B" or "C" if I select a Region, from the list box. Similarly, I want to count the number of Managers with a "A", "B" or "C" if I select a Zone and so on .....till Store. I want this to be displayed in a bar chart ( showing counts of "A" , "B", "C") where I will use the same Cyclic group , "Retail".

Any help to write this as an expression will be greatly appreciated.

Thanks,

Tutan

Re: Expression help

Anonymous — Sun, 05 Jan 2014 22:37:54 GMT

Hi Tutan,

I'm not sure if I understand your question correctly but I believe the key thing you are trying to do is count the elements in your cyclic location dimension by a category of "A", "B" or "C", is this correct? If so then I believe what you are describing is a calculated dimension, which is to say you want to count stores/zones/managers etc BY a category or dimension that you create. On the Dimensions tab of the chart properties you will see an "Add Calculated Dimension" button. You can use the number ranges you mentioned to create the dimension values for "A", "B" or "C" which will then be applied as a dimension in your bar chart. Your dimension expression might contain something like a nested IF statement or perhaps a Class or range function as required.

Cheers,

Rod

Re: Expression help

rbecher — Sun, 05 Jan 2014 22:40:05 GMT

Maybe you can upload an example for better understanding...

Re: Expression help

Mon, 06 Jan 2014 23:40:39 GMT

Here is an example: Please take a look into the straight table

Retail(Managers)	KPI1	KPI2	KPI1/KPI2
Manka	100	200	0.5	C
Tunai	300	250	1.2	A
Munai	275	300	0.916667	B
Taniya	442	414	1.067633	A

This is the table which shows the gradation of Managers based on KPI's.

I want a table like this (When I cycle to Zone) :

Retail(Zone)	No. of A	No. of B	No.of C
US East	2	1	1

This shows me the number of Managers under the Zone "US East" having A, B or C.

So, the hierarchy is Region---->Zone--->Managers--->Field_Employee---->Store. Store is the lowest level in the hierarchy of cyclic group.

Retail is the cyclic group where I have defined this hierarchy.

Thanks much in advance for your help!

Tutan

Re: Expression help

jagan — Tue, 07 Jan 2014 02:31:52 GMT

Hi,

Can you attach some sample data.

Regards,

Jagan.

Re: Expression help

Anonymous — Tue, 07 Jan 2014 02:53:01 GMT

It's a little hard to tell without some sample data, but if I assume your initial calculation of "A", "B", "C" is done via an expression similar to:

if(KPI1/KPI2 >= 1, 'A', if(KPI1/KPI2 > .5, 'B', 'C'))

Then to expression used for the count of 'A' in your second table would be something like:

Sum(if(KPI1/KPI2 >= 1, 1, 0))

Cheers,

Rod