Year over Year % Calculation in Pivot Table

[vvira1316]

Hi,

In an Excel source I have two different amounts data for different months (Jan - Dec), years (2013, 2014, 2015, 2016), and business categories. The Pivot Table shows two year worth of data based on Year selected by user. Year Selected and Previous Year. Rows show business categories. Year and Amount in columns. Now I have to show % change from Previous year for this selected year.

So I need to show %change and there should be only one column of it rather than two that would be shown by Pivot table for two different years.

I'm unable to get % working. Any ideas / suggestions will be helpful.

Thanks,

Vijay

[#2]

Hi Vijay

according to my understanding you want to calculate evolution from Y / Y-1?

(Year - PrevYear) / PrevYear

Is it?

Chris

[vvira1316]

Hi Chris,

Thanks a lot
for quick response.

Yes either
one is fine as long as I’m able to get % value for each row

in the set analysis. Currently it is not showing any value. Let me know if
you will like to have a screenshot, data set or any other info.

Regards,

Vijay

[#4]

May be you can post your expression, i can have a look

Chris

[vvira1316]

Hi Chris,

here is the information.

  Total Savings
Definition

if(vComparePeriods = 1, Sum ({$<[Year]={'$(=vYear)','$(=vYear
- 1)'}>}[Total Savings]),

Sum ({$<[Year]={'$(=vYear)'}>}[Total Savings])

)

% Total Savings Definition

if(vComparePeriods = 1, Sum ({$<[Year]={'$(=vYear)','$(=vYear
- 1)'}>}[Total Savings])/ Sum ({$<[Year]={'$(=vYear)','$(=vYear -
1)'}>}[Total Savings]),

Sum ({$<[Year]={'$(=vYear)'}>}[Total Savings])/ Sum ({$<[Year]={'$(=vYear)'}>}[Total Savings])

)
//=(sum(if([Year]
= $(vYear)-1, [Total Savings]) ) /sum(if([Year] = $(vYear), [Total
Savings])  ))*100

//(Sum
({$<[Year]={'$(=vYear - 1)'}>}[Total Savings]) /Sum
({$<[Year]={'$(=vYear)'}>}[Total Savings]) )*100



//(Sum
({$<[Year]={'$(=vYear - 1)'}>}{$<[Total Savings]={'$(=vYear -
1)'}>}[Total Savings]) / Sum ({$<[Year]={'$(=vYear)'}>}{$<[Total
Savings]={'$(=vYear)'}>}[Total Savings]))

//if
(Sum ({$<[Year]={'$(=vYear)'}>}[Total Savings]) <> 0,(Sum
({$<[Year]={'$(=vYear - 1)'}>}[Total Savings]) / Sum
({$<[Year]={'$(=vYear)'}>}[Total Savings]))*100)

variable
% Total Savings

=(Sum ({$<[Year]={'$(=vYear
- 1)'}>}[Total Savings]) / Sum ({$<[Year]={'$(=vYear)'}>}[Total
Savings]))

Regards,

Vijay

[vvira1316]

[vvira1316]

[#8]

Try like below:

First create the 2 variables:   LET vYear = Max(YEAR) ;  LET vPrevYear = Max(YEAR) -1 ;

Current Yr Total:  SUM({$<YEAR={$(vYear)}>} [Total Savings])

Prev Yr Total:  SUM({$<YEAR={$(vPrevYear)}>} [Total Savings])

Current + Prev Yr Total:  SUM({$<YEAR={$(vYear) , $(vPrevYear) }>} [Total Savings])

Current Yr / Prev Yr : SUM({$<YEAR={$(vYear)}>} [Total Savings]) / SUM({$<YEAR={$(vPrevYear)}>} [Total Savings])

(Curr Yr + Prev Yr) / Prev Yr : SUM({$<YEAR={$(vYear) , $(vPrevYear) }>} [Total Savings]) /  SUM({$<YEAR={$(vPrevYear)}>} [Total Savings])

[vvira1316]

Hi,

Thanks for the information. I didn't try in the pivot table but used it in straight table. I was able to achieve the functionality business user is looking for.

I have another question. In the screen shots attached for this question there are two charts in two container objects. Each container object contains % and $ charts. One container is for selected year and the other one is for previous year. Is there a way to control synchronous display in each container so say if user selects a % chart in either container the other container will also display % chart and vice versa for $ chart selection.

Another question is about message "No Data Available for Display'. Is there a way to capture this and not display the chart or display another appropriate message.

Regards,

Vijay

[#10]

1. Instead of containers, use the buttons to show/hide the Qlikview objects.

2. Use calculated chart condition (General --> Calculation Condition ) to show the Meaningful error messages (General --> Error Message --> Calculation Condition unfulfilled).

Please post sample qvw with reduced data.