How to make calculation in pivot table

[Shahzad_Ahsan]

Hi

I have pivot table like below in qlik sense. where "Name" is as dimension and Month-Year as Column and the numbers are in measures.

Name	Jan-19	Feb-19	Mar-19	Apr-19
A	100	50	36	65
B	200	2157	25	74
C	300	695	48	85
D	400	247	77	98

I need to calculate the below data on behalf of above table.

Here, the condition goes like this, Every column is divided by "Apr-19" column

Eg: 100/65=1.54

Category	Jan-19	Feb-19	Mar-19	Apr-19
1	1.54	0.77	0.55	1
2	2.70	29.15	0.34	1
3	3.53	8.18	0.56	1
4	4.08	2.52	0.79	1

Problem: when I am dividing Jan-19/Apr-19,  it always returns 0. 

[Shahzad_Ahsan]

Please find the attached sample qvf file.

[JordyWegman]

Hi Shahzad,

You have to force Qlik to divide by April. Do this by using set analysis. See the following:

Sum(YourAmountfield) / Sum({$< YourYearMonthField = {'Apr-19'}>} YourAmountField)

To make it more reliable, you should create a variable with the last complete month and put this $(variable) in 'Apr-19' in the set analysis.

Jordy

Climber

[Shahzad_Ahsan]

Hi @JordyWegman 

Thanks for your response.

I have already tried this one , but not working

Please find the attached qvf and check the 2nd pivot table.

I am trying to divide the total amount by Mar-2019. All returns 0 and the last column returns 1, that means  the expression below is returning 0 for all accept Mar-2019

Sum({$< YourYearMonthField = {'Apr-19'}>} YourAmountField)

[sunny_talwar · Accepted solution]

Try this

Sum(Amount)/Sum(TOTAL <Bkt, Dpd> {<[Month-Year] = {"$(=Date(Max([Month-Year])))"}>} Amount)

But before you try this for your sample, change the script for the second table to this

Table2:
LOAD Month,
If(date(date#(Month, 'MMM-YYYY'), 'MM/DD/YYYY') >= AddMonths(Today(), -14), date(date#(Month, 'MMM-YYYY'))) as [Month-Year]
Resident Table1;

[Shahzad_Ahsan]

Hi @sunny_talwar 

Thanks for your response. 

Your suggestion is working fine with Max. But suppose I have to divide by a particular column, that means,(Jan-2018) which is not on the pivot table, it returns 0 

I am showing only last 1 year data in the table.

[sunny_talwar]

I guess you already have your set analysis figured out, right? All you need is to add TOTAL <Bkt, Dpd> in your expression to make it repeat the Jan-2018 value for other Months

[sunny_talwar]

Something like this

Sum(Amount)/Sum(TOTAL <Bkt, Dpd> {<[Month-Year] = {"Jan-2018"}>} Amount)

[Shahzad_Ahsan]

I think I am unable to explain

What I am trying to say is, the below expression is returning 0.  because, Jan-2018 is not in the pivot table column but is available in the data.

Sum(TOTAL <Bkt, Dpd> {<[Month-Year] = {"Jan-2018"}>} Amount)

[Shahzad_Ahsan]

The below expression which you sent before is working fine.

Sum(Amount)/Sum(TOTAL <Bkt, Dpd> {<[Month-Year] = {"$(=Date(Max([Month-Year])))"}>} Amount)