Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

calculating prior periods

I am trying to create a report that will compare the current month to the prior month.  The rub is that all 12 months will be on columns (as shown).  I can determine what the prior period is (in February, the prior period will be January, in March it will be February and so on).  However, when I try to create the following set analysis , it doesnt return a value.
=sum ({$<MONTH ={$(=$(#varPriorMonth)) }>} FORECAST_VALUE)
this is a variable that calculates the prior month and it does return a value if I create a simple expression like =$(varPriorMonth) as shown.
Can I not accomplish this with set analysis?
Thanks,
Joe
9 Replies
swuehl
MVP
MVP

Hi,

I believe the set expression is evaluated only once and will not take your month dimensions into account (I think your variable will acces this dimension).

Stefan

swuehl
MVP
MVP

Maybe you could use something like

=sum(aggr(if(MONTH =$(#varPriorMonth), FORECAST_VALUE)), MONTH)

as expression.

Maybe a need a total or missed a bracket, not tested yet 😉

Stefan

P.S:

you probaly have more than one FORECAST per month,

so

=sum(aggr(sum(if(MONTH =$(#varPriorMonth), FORECAST_VALUE))), MONTH)

should be better?

edited by swuehl

Not applicable
Author

Hi, I think it might be easier to use chart inter-record functions to accomplish this task, like the above funtion. Similiar to the following:

rangesum(above("Amount"))

Notice the rangesum function is used to return either the value of the previous month or zero, when the column is january.

Regards

johnw
MVP
MVP

Above() works as long as the chart remains sorted in a specific order and you don't need a previous month for the very first month in the chart.  If you need to get around these limitations, you can also search the forum for 'AsOf' for examples of handling this sort of thing with data.  Briefly:

AsOf:
AsOfMonth, MonthType, Month
August 2011, Current, August 2011
August 2011, Previous, July 2011
July 2011, Current, July 2011
July 2011, Previous, June 2011
etc.

Use AsOfMonth instead of Month in the chart and make MonthType a new chart dimension.  Now you only need one expression instead of two.

Edit: This approach also has weaknesses, primary I think that you now have two month fields instead of one, which can cause confusion when making selections.  If you select July 2011 in the Month field, for instance, this chart will show both August and July, with the same numbers, and August will have the numbers in the previous month column, while July will have it in the current month column.  It's correct, but probably not what you intended to look at when you selected that month.

stevedark
Luminary Alumni
Luminary Alumni

Hi Joe - I have uploaded a shared QlikView that may give you some assistance with Prior Periods using Set Analsyis: http://community.qlik.com/qlikviews/1175

- Steve

swarup_malli
Specialist
Specialist

That was a good application u uploaded,

   =SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}Amount)

Could anyone  tell me the significance of  the code i have highlighted in red, as there is no value after "=" sign...

Not applicable
Author

the code highlighted in red will force the expression to ignore any selections in those fields.

swarup_malli
Specialist
Specialist

Thank you Tammy !!!

stevedark
Luminary Alumni
Luminary Alumni

Thanks MS.  I'm glad it was helpful.  I've uploaded a couple of others too - just need to tidy up some more before they go up also.

Thanks also Tammy for fielding the 'ignore' query.

- Steve