Announcements
cancel
Showing results for
Did you mean:
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) -

/

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:

9 Replies
MVP

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

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!

MVP

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

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.

Partner - Creator III
Author

Hi Qlikers,

Could anyone help me with this?

Regards,

Mohan

Contributor

Hi dear,

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

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

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

Hi,

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