Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have used this expression to calaculate Growth rate. But i am not getting any answer in the text box.
=num(((Sum({<[YYYYQQ]={$(=(vCurrentQuarter))}>}[Quantity])-Sum({<[YYYYQQ]={$(=(vPreviousYearQuarter))}>}[Quantity])))/(Sum({<[YYYYQQ]={$(=(vCurrentQuarter))}>}[Quantity])),'##0%' )
I have defined the above variables
Is there anything wrong in the expression?
YYYYMM | Financial Year | Month | Quarter | YYYYQQ | Quantity |
201411 | 2015 | Nov | Q1 | 2015Q1 | 100 |
201412 | 2015 | Dec | Q1 | 2015Q1 | 304 |
201501 | 2015 | Jan | Q1 | 2015Q1 | 564 |
201502 | 2015 | Feb | Q2 | 2015Q2 | 345 |
201503 | 2015 | Mar | Q2 | 2015Q2 | 356 |
201504 | 2015 | Apr | Q2 | 2015Q2 | 356 |
201505 | 2015 | May | Q3 | 2015Q3 | 353 |
201506 | 2015 | Jun | Q3 | 2015Q3 | 325 |
201507 | 2015 | Jul | Q3 | 2015Q3 | 335 |
I suggest using YYYYMM field to do the manipulation rather then using the YYYYQQ. But before using YYYYMM, make sure it is formatted a date rather than a number.
Script:
Table:
LOAD Date(MonthStart(Date#(YYYYMM, 'YYYYMM')), 'YYYYMM') as YYYYMM,
[Financial Year],
Month,
Quarter,
YYYYQQ,
Quantity
FROM
[https://community.qlik.com/thread/217769]
(html, codepage is 1252, embedded labels, table is @1);
Expression:
=Num(((
Sum({<[YYYYMM]={$(=Date(Max(YYYYMM), 'YYYYMM'))}>}[Quantity])-
Sum({<[YYYYMM]={$(=Date(AddMonths(Max(YYYYMM), -3), 'YYYYMM'))}>}[Quantity])))
/
(Sum({<[YYYYMM]={$(=Date(Max(YYYYMM), 'YYYYMM'))}>}[Quantity])),'##0%')
I suggest using YYYYMM field to do the manipulation rather then using the YYYYQQ. But before using YYYYMM, make sure it is formatted a date rather than a number.
Script:
Table:
LOAD Date(MonthStart(Date#(YYYYMM, 'YYYYMM')), 'YYYYMM') as YYYYMM,
[Financial Year],
Month,
Quarter,
YYYYQQ,
Quantity
FROM
[https://community.qlik.com/thread/217769]
(html, codepage is 1252, embedded labels, table is @1);
Expression:
=Num(((
Sum({<[YYYYMM]={$(=Date(Max(YYYYMM), 'YYYYMM'))}>}[Quantity])-
Sum({<[YYYYMM]={$(=Date(AddMonths(Max(YYYYMM), -3), 'YYYYMM'))}>}[Quantity])))
/
(Sum({<[YYYYMM]={$(=Date(Max(YYYYMM), 'YYYYMM'))}>}[Quantity])),'##0%')
Hey Sunny,
Thanks for your help.
I am now able to do it.