QOQ variance Calculation for First Quarter

[joshibabu_are]

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

[Anil_Babu_Samineni]

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

[joshibabu_are]

hi,

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

hard coded figures should be replaced with Expression

[Anil_Babu_Samineni]

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)

[joshibabu_are]

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

[joshibabu_are]

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

[Anil_Babu_Samineni]

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)

[joshibabu_are]

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

[sunny_talwar]

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

[joshibabu_are]

hi Sunny,

if nothing selected Max year data it should show