Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mohan_1105
Contributor III

How to show the growth% in YTD/QTD/MTD in Pivot table?

Hi Qliker,

I wanted to show Growth % for Sales in the pivot table having dimensions shown in below snippet. I'm using the following expression to calculate the growth%.

sum({$<FiscalYear={"$(=Max(FiscalYear))"},Date={"<=$(=Max(Date))"}>}sales) -

sum({$<FiscalYear={"$(=(YEAR(Max(FiscalYear)))-1)"},Date={"<=$(=AddMonths(Max(Date),-12))"}>}sales)

/

sum({$<FiscalYear={"$(=(YEAR(Max(FiscalYear)))-1)"},Date={"<=$(=AddMonths(Max(Date),-12))"}>}sales)

It works fine if I have only Fiscal Quarter and Month as the row dimensions. When I add the fiscal year in rows dimensions, I get the wrong output displayed in below snippet:

Capture2.JPG

Thanks in advance..!!!

Tags (2)
5 Replies
MVP
MVP

Re: How to show the growth% in YTD/QTD/MTD in Pivot table?

Since year is part of the dimension, you will now need use Above()/Below() function to do this

Missing Manual - Above() and Below()

Alternatively, you can use

The As-Of Table

which is a much more robust approach to solving this type of problem.

Best,

Sunny

mohan_1105
Contributor III

Re: How to show the growth% in YTD/QTD/MTD in Pivot table?

Hi Sunny,

I tried using above function, it fails because,

For,

YTD - It calculates, growth% with this year with the previous whole year instead of calculating this year with previous year till date.

QTD and MTD - It calculates, growth% with current Quarter/Month with Previous Quarter/Month but the requirement is to calculate using current year current Month/Quarter with previous year same Quarter/Month.

Kindly help!

MVP
MVP

Re: How to show the growth% in YTD/QTD/MTD in Pivot table?

Would you be able to share a sample to help you better here?

mohan_1105
Contributor III

Re: How to show the growth% in YTD/QTD/MTD in Pivot table?

Hi stalwar1,

Please find the attached app. !

In the attached app, I tried to calculate growth% for the fiscal quarter and month it is working fine. But I swap the fiscal year as the first dimension in the pivot table calculation doesn't work correctly.

Thanks in advance..!! and kindly excuse my delayed reply.

mohan_1105
Contributor III

Re: How to show the growth% in YTD/QTD/MTD in Pivot table?

Hi Qlikers,

Could anyone help me with this?

Thanks in advance..!!

Regards,

Mohan

Community Browser