Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to compute CY vs LY accumulated % var to make it display +4% ?

Hi All

Below expression plot the CY line chart :-

Money(RangeSum(Above(TOTAL Sum(DISTINCT{<year = {"$(=Max(year)-0)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, RowNo(TOTAL))), $(vMoneyFormatK))

Below expression plot the PY line chart :-

Money(RangeSum(Above(TOTAL Sum(DISTINCT{<year = {"$(=Max(year)-1)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, RowNo(TOTAL))), $(vMoneyFormatK))

I try to create % Var line using below expression :-

(

(

(

Money(RangeSum(Above(TOTAL Sum(DISTINCT{<year = {"$(=Max(year)-0)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, RowNo(TOTAL))), $(vMoneyFormatK))

)

/

(

Money(RangeSum(Above(TOTAL Sum(DISTINCT{<year = {"$(=Max(year)-1)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, RowNo(TOTAL))), $(vMoneyFormatK))

)

)

-1)*1

The end % i get equal -376% which is wrong , may i know how to get +4 % ?

Paul

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Why don't you try with your label of your expression?  Like

(CY/PY-1)*1

And, Check the 'No Accumulation' in Expression Tab.

Capture.JPG

View solution in original post

8 Replies
settu_periasamy
Master III
Master III

Why don't you try with your label of your expression?  Like

(CY/PY-1)*1

And, Check the 'No Accumulation' in Expression Tab.

Capture.JPG

paulyeo11
Master
Master
Author

Hi Settu

You got it right . Thank you .  may i know how to make the % Var Y Axis scale start from -60% ( as zero level ) . So that the var % will be supper impose at CY & PY line. i mean all 3 line in 1 area , right now the chart look a bit funny.

Paul

paulyeo11
Master
Master
Author

chart % var.pngH

How to make it Look some thing like this.

settu_periasamy
Master III
Master III

Hi Paul,

Already i tried to do that what you have said with combo chart..

Right Axis as Variance %. But no luck. Will check that again..

settu_periasamy
Master III
Master III

Hi,

How is this one?

Capture.JPG

Capture.JPG

paulyeo11
Master
Master
Author

Hi SEttu

Thank you very much , it work fine now.

I try to create the % var expression , but it does not work , can you advise me why ? As i need to apply in QS using the expression.

rangesum(above(

Sum(DISTINCT{<year = {"$(=Max(year)-0)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000

,0,rowno())) /

rangesum(above(

Sum(DISTINCT{<year = {"$(=Max(year)-1)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000

,0,rowno())) -1

Paul

settu_periasamy
Master III
Master III

Hi Paul,

Did you try the already suggested expression? It's working fine for me..

(CY/PY-1)*1

paulyeo11
Master
Master
Author

Yes it fine too. Thank you