Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Thanks in advance..!!!
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
which is a much more robust approach to solving this type of problem.
Best,
Sunny
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!
Would you be able to share a sample to help you better here?
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.
Hi Qlikers,
Could anyone help me with this?
Thanks in advance..!!
Regards,
Mohan
Hi dear,
Have you solved this problem?I am facing the same situation for year.
Could you please help
Have you tried the AsOfTable as mentioned above
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130
@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.
Hi,
I calculated the values in the backend and simply showed the values in the pivot table.