Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Total units previous Period

I have a requirement where I need to create a Current period and Previous period showcase on my App.

For Total Units for Selected Period I`m using =sum(Units)

The Select Period is derived from Fiscal Year & Quarters..

So based on what my user would select as Currently period, I`d also want to show the same Data for previous period.

IS this what I need to do ?

set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');

And then the expressions for the current month and prior month columns is:

Sum({<[Month Year]={'$(vPriorMonthYear)'},Year=,Month=,Date=>}Units)

Can someone help me with this ??

1 Reply

Re: Total units previous Period

One possibility is to generate an AsOf table:

AsOf Month Year,Month Type,Month Year




Use Month Type as a dimension in your chart, sum(Units) as your expression. No variables, no set analysis, all complexity in the script instead of the chart, which is where I prefer to keep it when practical.

This may not be the right solution for your specific requirement. Like the chart I suggest doesn't make it easy to then, say, add the difference in units between the months as another column. You might handle that by removing the Month Type dimension and having three expressions:

Selected = sum({<[Month Type]={'Selected'}>} Units)
Previous = sum({<[Month Type]={'Previous'}>} Units)
Difference = Selected - Previous

Or you could go nuts and handle that in script too:

AsOf Month Year,Month Type,Month Year,Sign

Add the Month Type back in as a dimension, and change the expression to:

sum(Sign * Units)

I probably wouldn't go that far, but maybe I would. It would depend on the situation I guess.