topic Re: QOQ variance Calculation for First Quarter in QlikView

QOQ variance Calculation for First Quarter

joshibabu_are — Thu, 16 Mar 2017 19:59:38 GMT

Hi All,

I want to show QoQ Varaiance.

Expression For 2017 Q1 Variance:

(Q4 Amount -Q1 Amount )/Q1*100

Same Logic For All Quarters .

I want dynamic Expression to achieve this .

please find the attached screen shot and data file and Qvf file

Result for 2017 Q1 should be: -20%

Any Help would be greatly appreciated.

stalwar1‌

Thanks,

Joshi

Re: QOQ variance Calculation for First Quarter

Anil_Babu_Samineni — Fri, 17 Mar 2017 05:37:51 GMT

What are these (400-500)/(500)

Re: QOQ variance Calculation for First Quarter

joshibabu_are — Fri, 17 Mar 2017 06:43:32 GMT

hi,

2016 Q4 Amount - 2017 Q1 Amount/2017 Q1 Amount.

hard coded figures should be replaced with Expression

Re: QOQ variance Calculation for First Quarter

Anil_Babu_Samineni — Fri, 17 Mar 2017 06:56:50 GMT

True, But when i filtering from Pane why i got 800 instead of 400 ??

From Expression prospective, Can you try a luck as Static

(sum({<Quarter = {'Q4'}, Year = {'2016'}>}Amount)-sum({<Quarter = {'Q1'}, Year = {'2017'}>}Amount))/sum({<Quarter = {'Q1'}, Year = {'2017'}>}Amount)

Re: QOQ variance Calculation for First Quarter

joshibabu_are — Fri, 17 Mar 2017 07:09:31 GMT

Hi Anil,

Thanks for your time

800 is sum for 2017.

Division 1 400+Division2 400=800

i used ur expression but it showing -100%

Thanks,

joshi

Re: QOQ variance Calculation for First Quarter

joshibabu_are — Fri, 17 Mar 2017 07:12:15 GMT

hi Anil,

if i use Aggr its showing correct for your hard coded expression.

Aggr((sum({<Quarter = {'Q4'}, Year = {'2016'}>}Amount)-sum({<Quarter = {'Q1'},

Year = {'2017'}>}Amount))/sum({<Quarter = {'Q1'}, Year = {'2017'}>}Amount),Division)

how to achieve the same with Dynamic Expression?

Thanks,

Joshi

Re: QOQ variance Calculation for First Quarter

Anil_Babu_Samineni — Fri, 17 Mar 2017 07:30:09 GMT

Then may be use this?

Aggr((sum({<Quarter = {'$(=MaxString(Quarter))'}, Year = {'$(=Max(Year-1))'}>}Amount) - sum({<Quarter = {'$(=MinString(Quarter))'},Year = {'$(=Max(Year))'} >}Amount)) / sum({<Quarter = {'$(=MinString(Quarter))'}, Year = {'$(=Max(Year))'}>}Amount),Division)

Re: QOQ variance Calculation for First Quarter

joshibabu_are — Fri, 17 Mar 2017 07:43:50 GMT

Hi ,

thanks for your time spent to help me out.

Its working fine for 2017 Q1 but Q2,Q3,Q4 its showing blank QoQ variance %.

2017 Q2 variance =2017 Q1 - 2017 Q2 / 2017 Q2

Thanks,

Joshi

Re: QOQ variance Calculation for First Quarter

sunny_talwar — Fri, 17 Mar 2017 08:41:15 GMT

Check the attached... once you select 2017... it will work... but what do you want to see when nothing is selected?

Re: QOQ variance Calculation for First Quarter

joshibabu_are — Fri, 17 Mar 2017 08:43:14 GMT

hi Sunny,

if nothing selected Max year data it should show

Re: QOQ variance Calculation for First Quarter

sunny_talwar — Fri, 17 Mar 2017 08:45:11 GMT

When you say max year, do you mean the same data that we see when 2017 is selected or data when nothing is selected?

Re: QOQ variance Calculation for First Quarter

sunny_talwar — Fri, 17 Mar 2017 08:46:01 GMT

Try this:

Aggr(Above(Sum({<Year, Quarter, YearQuarter>}Amount))/Sum({<Year = {"$(=Max(Year))"}>}Amount)-1, Division, (YearQuarter, (Text)))

Re: QOQ variance Calculation for First Quarter

joshibabu_are — Fri, 17 Mar 2017 09:44:45 GMT

Hi Sunny,

Thanks for your Help.its working Perfect.

but small change in requirement.

how to change your expression for below case

2017 Q1 Amount-2016 Q4 Amount/2016 Q4 Amount.

Thanks,

joshi

Re: QOQ variance Calculation for First Quarter

sunny_talwar — Fri, 17 Mar 2017 09:49:07 GMT

This should do it

Aggr(Sum({<Year = {"$(=Max(Year))"}>}Amount)/Above(Sum({<Year, Quarter, YearQuarter>}Amount))-1, Division, (YearQuarter, (Text)))