topic Get previous period in QlikView

Get previous period

Tue, 22 Mar 2011 13:55:36 GMT

Hello All,

I have the next formula to get values from the previous year;

Sum ({<[Fiscal year]={$(=maxstring([PIT year])-1)},[Fiscal period]={$(=maxstring([PIT period]))}>}[Total branches])

Working fine!

Now i want to get values from the previous month, I've tried this;

(Months are , 01, 02, 03, 04,........,12)

Sum ({<[Fiscal year]={$(=maxstring([PIT year]))},[Fiscal period]={$(=maxstring([PIT period])-1)}>}[Total branches])

This one isn't working, so I tried this one

Sum ({<[Fiscal year]={$(=maxstring([PIT year]))},[Fiscal period]={$(=RIGHT('00'&maxstring([PIT period])-1,2))}>}[Total branches])

Also not working

Of course I also have to do an if function to get the value for month '01' right, but first I want to solve above.

Can anyone give me a hand?, Thnx in advance

Get previous period

Tue, 22 Mar 2011 14:07:26 GMT

You will need a date field, instead of doing the year and the month separately. And from that date field you need to subtract 1 month using the AddMonths function for example.

Get previous period

Tue, 22 Mar 2011 14:28:29 GMT

Tried this one, but isn't working

Sum ({<[Fiscal year]={$(=maxstring([PIT year]))},[Fiscal period]={Month( AddMonths('01-'&$(=maxstring([PIT period]))&'-'&$(=maxstring([PIT year])),-1))}>}Branches)

Get previous period

Tue, 22 Mar 2011 15:31:48 GMT

Anyone another hint, you would help me out a lot!

Get previous period

Oleg_Troyansky — Tue, 22 Mar 2011 15:44:37 GMT

First, I'm wondering why do you need ot use MaxString - are your Years and Months represented as strings?

The best advise I can give is not to overly complicate your Set Analysis conditions, but rather to pre-calculate Flags in your load script for Prior Year, Prior Month, etc... You can use IF statements to check if certain Date belongs to the Prior Year or to Prior Period, or to Rolling 12 Months, etc...

Then your Set Analysis will always be very simple :

sum( {<PriorYearFlag = {1}>} ...)

Get previous period

stevedark — Wed, 16 Nov 2011 21:09:42 GMT

Hi there - I've uploaded a shared QlikView with an example of Prior Period comparisons - this may be of some help to you?

http://community.qlik.com/qlikviews/1175

Steve

http://www.quickintelligence.co.uk/