Hi,
The requirements I have are to calculate a percent change between the current month and prior months values. Then also calculate the YTD average change for all months in the year.
So if my data looks like this...
| Month | Current Month |
| Dec | 50 |
| Jan | 55 |
| Feb | 60 |
| Mar | 65 |
My Percent change for March would be (65-60)/65 = 7.69%. And the table for the year would look as such:
| Month | Current Month | Prior Month | % Change |
| Dec | 50 | - | - |
| Jan | 55 | 50 | 9.09% |
| Feb | 60 | 55 | 8.33% |
| Mar | 65 | 60 | 7.69% |
My calculate for percent change is something similar to:
(SUM({$<Month = {$(=Only(Month))}>} - SUM({$<Month = {$(=Only(Month) - 1)}>} )/ SUM({$<Month = {$(=Only(Month))}>}
Now they want to get the average YTD change percent. So the YTD change for Feb would be (9.09+8.33)/2 = 8.71%. And I would end up with this table:
| Month | Current Month | Prior Month | % Change | Avg YTD % change |
| Dec | 50 | - | - | - |
| Jan | 55 | 50 | 9.09% | 9.09% |
| Feb | 60 | 55 | 8.33% | 8.71% |
| Mar | 65 | 60 | 7.69% | 8.37% |
Now the question comes in. How can I calculate this Avg YTD % change using QlikView? I would imagine it owuld need to be something like
avg(
aggr(
(Calculate % change),
[Month]
)
)
but i am not sure how to calculate the % change correctly when doing this over all months. Any help would be appreciated.
Thanks in advanced
