Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Having several aggregations in pivot table.

This is my example data:

ID 1Param1

Param2

1

32
156
237
2410


I want this (in form of a pivot table perhaps):


    |          param1        |       param2        |

----------------------------------------------------

id | avg | min | max |   |  avg | min | max |

----------------------------------------------------

1 |   4    |  3   |   5    |   |   4    |  2    |  6     |

2 |   3.5 |  3   |   4    |   |   8.5 | 7     |  10   |

Now I know I could do this (without the headers in pivot table 2 (param1, param2) by just creating measures with the aggr (min, max etc) but it will becomes very tedious when I have 10 parameters and 7 aggregations (min, max, median, avg, std, skew, count). Is there any simple way to make this automated? For instance, I just choose the parameter and it automatically creates 7 measures for the given parameter. Note, this concerns Qlik Sense.

5 Replies

Re: Having several aggregations in pivot table.

Create a Parameter field in the script and use that as dimension:

CrossTable(Parameter,Value)

LOAD

     ID1,

     Param1,

     Param2

FROM

     ...mysource...

;


talk is cheap, supply exceeds demand
reddys310
Honored Contributor II

Re: Having several aggregations in pivot table.

Hi Samir,

Check this out:

The Crosstable Load

Thanks and Regards,

Sangram Reddy.

MVP
MVP

Re: Having several aggregations in pivot table.

Hi,

You can try CrossTable() to unpivot the data.  Check below link for sample script

Sample Cross Table script

Regards,

Jagan.

Not applicable

Re: Having several aggregations in pivot table.

But you still need to create 7 measures for each of the parameters, meaning if you have 10 parameters, you need to create 70 measures (min, max, median, avg, skew, std, count)?

MVP
MVP

Re: Having several aggregations in pivot table.

No, I don't think that is necessary. Parameter is a dimension (if you cross table load as suggested), and you then need one expression for each aggregate (3 in your original post - avg(), min() and max()).