Get previous period

[#1]

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

[#2]

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.

[#3]

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)

[#4]

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

[Oleg_Troyansky]

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

[stevedark]

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/