Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

hannibal2395
New Contributor II

Calculated measure in pivot table in qlik sense

Hi,

I have been trying to use calculated measure in pivot table but it is not giving me desire output.

CustomerJan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 
 TypeCountTypeCountTypeCountTypeCountTypeCountTypeCountTypeCountTypeCountTypeCountTypeCountTypeCountTypeCount
BACOF1- - - - - - -    - - -
XYZ- - - - 1 - - - -  - - -
PQR- - - - 1 - - - -  - - -
STU- - - - - - - - -  - 1 -
XYZ- - - 1 - - - - -  - - -

This is my pivot table and in this Type and Count are my calculated measures.

But i want both of them separated at the end or at the beginning after customer column like below: 

CustomerJan-2018Feb-2018Mar-2018Apr-2018May-2018Jun-2018Jul-2018Aug-2018Sep-2018Oct-2018Nov-2018Dec-2018CountType
               
BAC---------2--2SM
XYZ-----1------1OF
PQR-----1111---4REP
STU-2----21134114REG
XYZ----1-------1OF

 

How am i supposed to do that?

Please Suggest.

 

4 Replies
MVP & Luminary
MVP & Luminary

Re: Calculated measure in pivot table in qlik sense

Create a dimension that contains both the month values and the values 'Count' and 'Type'. That's the only way to create that kind of pivot table. It needs one single dimension for values for the columns. You'll probably need a monster of an expression as measure as well. I see the expression sometimes has to return a numeric count and sometimes a string value that looks like a label of a Type. And no idea what happens if there are several Type labels for Customer.


talk is cheap, supply exceeds demand
hannibal2395
New Contributor II

Re: Calculated measure in pivot table in qlik sense

Thanks for the reply.

Could you suggest me any other way around it.

if i want same in the normal table then what do i have to do.

MVP & Luminary
MVP & Luminary

Re: Calculated measure in pivot table in qlik sense

In a normal table you can add separate measures for each column. You'll have to add 12 measure for each month and the one for Count and one for Type. For the month columns you can use set analysis to select the month. For example count(Item) would become count( {<[My Month Field] = {'Jan-2018'} >} Item)

Note: replace [My Month Field] and 'Jan-2018' with the real name of your month field and the real value of januari 2018 as it exists in your month field


talk is cheap, supply exceeds demand
hannibal2395
New Contributor II

Re: Calculated measure in pivot table in qlik sense

Thanks Gysbert it worked fine.

But it is like hard coded after this if year changes then again i will have to redo it.

Is there any way that i could make it dynamic?