Dynamic dimension

[kumarkp412]

Hi friends

I were struck in creating the dynamic dimension by an expression with a category.

i.e Category as A,B,C,D,E.

I need to create a pivot table how many employees having average value  were layed in that category.

Below is the category creation.

Category Name ,Value.

A - >8

B - 6 and 7

C - 4 and 5

D -  <4

E- 0

Average value-sum(value)/count(months)

how we will relate the average values in the above category.

Sample data

Empid	value	month
A1	5	Jan
A2	8	Jan
A1	2	Feb
A2	8	Feb
A3	10	Feb

Final output:

Categroy	no of employess
A	2
B	0
C	1
D	0
E	0

Can any one help me on this.

Please provide me any suggestions.

Thanks

Kumar KVP

[kaushiknsolanki]

Hi,

Try interval match function to achieve this.

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/ScriptPrefixes/Inte...

Regards,

Kaushik Solanki

[kumarkp412]

Hi Kaushik,

Thanks for your reply,

I am having one doubt in that, when I filter a particular month it should calculate the average  based on that i.e sum(values for selected months )/count(selected months) .

So here how it will be give you the dynamic category.

Thanks in advance.

Thanks

Kumar KVP

[dwforest]

this is the default behavior of Qlik, measures are recalculated based on current selection unless a set expression is added to override the selections

[kumarkp412]

Hi David,

Yes your right, but my question is ,

when we make a selection on the months, average value calculation  should be recalculated and based on the average i need to show that which category  he belongs.

I am able to get the average calculation,but i need to show the chart based on the category. So here struct ...

Please advice me how can i able to achieve this.

Thanks in advance,

Thanks

Kumar KVP

[dwforest]

Still sounds to me like standard Qlik behavior; have you tried building it and not gotten a working solution?

The Category would be built something like:

if(Avg(value)>8, 'A',

     if(Avg(value)>=6, 'B',

         if(Avg(value)>=4, 'C',       

              if(Avg(value)>0, 'D','E'))))