topic Re: Year over Year % Calculation in Pivot Table in QlikView

Year over Year % Calculation in Pivot Table

vvira1316 — Thu, 19 Dec 2013 16:10:19 GMT

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

Re: Year over Year % Calculation in Pivot Table

Thu, 19 Dec 2013 16:19:23 GMT

Hi Vijay

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

(Year - PrevYear) / PrevYear

Is it?

Chris

Re: Year over Year % Calculation in Pivot Table

vvira1316 — Thu, 19 Dec 2013 16:30:48 GMT

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

Re: Year over Year % Calculation in Pivot Table

Thu, 19 Dec 2013 16:32:09 GMT

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

Chris

Re: Re: Year over Year % Calculation in Pivot Table

vvira1316 — Thu, 19 Dec 2013 17:05:16 GMT

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

Re: Re: Year over Year % Calculation in Pivot Table

vvira1316 — Thu, 19 Dec 2013 17:06:10 GMT

Re: Re: Year over Year % Calculation in Pivot Table

vvira1316 — Thu, 19 Dec 2013 17:08:00 GMT

Re: Year over Year % Calculation in Pivot Table

Thu, 19 Dec 2013 17:19:48 GMT

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])

Re: Re: Year over Year % Calculation in Pivot Table

vvira1316 — Thu, 19 Dec 2013 18:51:49 GMT

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

Re: Year over Year % Calculation in Pivot Table

Thu, 19 Dec 2013 19:56:46 GMT

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.