Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
hannibal2395
Contributor II
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
Gysbert_Wassenaar

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
Contributor II
Contributor II
Author

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.

Gysbert_Wassenaar

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
Contributor II
Contributor II
Author

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?