Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan_1105
Partner - Creator III
Partner - Creator 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..!!!

9 Replies
sunny_talwar

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
Partner - Creator III
Partner - Creator III
Author

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!

sunny_talwar

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

mohan_1105
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

Hi Qlikers,

Could anyone help me with this?

Thanks in advance..!!

Regards,

Mohan

shizaixiangbudao
Contributor
Contributor

Hi dear,

Have you solved this problem?I am facing the same situation for year.

Could you please help

robert99
Specialist III
Specialist III

Have you tried the AsOfTable as mentioned above

 

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

SonOfJeffGoldblum
Contributor III
Contributor III

@mohan_1105  did you ever find a solution to this issue?

I am facing a similar item where I have only month-end dates, and can pull up each value individually but they do not work when subtracted from one another. Your solution has shown most promise, but had to add in Above() to one sum. However it isn't the full solution.

Everything not saved will be lost
— Nintendo Quit screen message
mohan_1105
Partner - Creator III
Partner - Creator III
Author

Hi,

I calculated the values in the backend and simply showed the values in the pivot table.