Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

Re: Total units previous Period

One possibility is to generate an AsOf table:

AsOf Month Year,Month Type,Month Year

Apr-2016,Selected,Apr-2016

Apr-2016,Previous,Mar-2016

Mar-2016,Selected,Mar-2016
Mar-2016,Previous,Feb-2016

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
Apr-2016,Selected,Apr-2016,+1
Apr-2016,Previous,Mar-2016,+1
Apr-2016,Difference,Apr-2016,+1
Apr-2016,Difference,Mar-2016,-1

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.

Community Browser