Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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.
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
Change your load to this
Table2:
LOAD
Month,
date(date#(Month, 'MMM-YYYY')) as [Month-Year]
Resident Table1;
and then check Jan-2018
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 |
Don't know what the goal is, but try this
Sum(TOTAL{<[Month] = {"Jan-2018"}>} Amount)
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
Something like this
Sum(Amount)/RangeSum(Before(Sum(Amount), 1, 3))
or something similar based on your chart and sort order of MonthYear field