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. 

[sunny_talwar]

Would you be able to show a sample where you are trying it out? Sample shared with us doesn't seem to have Jan-2018?

[Shahzad_Ahsan]

Hi @sunny_talwar 

Please find attached sample qvf.

Check "Sheet 2"

Jan-2019 is showing data but Jan-2018 is showing 0. I need the value for Jan-2018 as well.  

[sunny_talwar]

Here is your expression for Jan-2018
Sum(TOTAL <Bkt, Dpd> {<[Month-Year] = {'1/1/2018'}>} Amount)

Now for this to work, you would expect Month-Year to have a value of 1/1/2018, right? or do you imagine that Qlik will create them from somewhere? These are the Month-Year available and I am not seeing 1/1/2018

[Shahzad_Ahsan]

Hi Sunny
Please check the field "Month" . that contains all data from 2017.

[sunny_talwar]

Change your load to this

Table2:
LOAD
Month,
date(date#(Month, 'MMM-YYYY')) as [Month-Year]
Resident Table1;

and then check Jan-2018

[Shahzad_Ahsan]

I got it what you are trying to show but I want to show only last 1 year data on the pivot table. that means from Mar-2018 to Mar-2019 

At the same time want to pick Jan-2018 value also

Suppose Jan-19 Amount is 200 and Jan-18 Amount is 50 so I need the result like this.

I dont want to show Jan-18 as column

	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19
Jan-19	200	200	200	200	200	200	200	200	200	200	200	200	200
Jan-18	50	50	50	50	50	50	50	50	50	50	50	50	50

[sunny_talwar]

Don't know what the goal is, but try this

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

[Shahzad_Ahsan]

Hi Sunny

Thanks for your support. This is what I needed. It looks strange what I am trying to achieve.

But I have last question related to this.

How can I get previous columns

Suppose If we are calculating for Jan-2019

Value for Jan-19 = Jan-19/Dec-18+Nov-18+Oct-18

[sunny_talwar]

Something like this
Sum(Amount)/RangeSum(Before(Sum(Amount), 1, 3))

or something similar based on your chart and sort order of MonthYear field

[Shahzad_Ahsan]

Hi Sunny
Thank you for all the support.
This is working fine
Highly Appreciated !!