calculating prior periods

[#1]

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

[swuehl]

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]

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

[#4]

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]

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]

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]

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...

[#8]

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

[swarup_malli]

Thank you Tammy !!!

[stevedark]

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