topic Re: Previous Month in QlikView

Previous Month

Wed, 03 Oct 2012 15:41:31 GMT

Hello ppl!

I have a question!

Ived created a variable called vPreviousMonth =month(addmonths(max(Date_Clean),-1))

I want a straight table which shows current month i have selected (ive made that) and then i want the previous table to show previous month

e.g. If i am in November i also want to see October - If i am in July, i also want to see June..

How do i make that expression?

My expression for currentmonth looks like this:

=Sum({<Month=, Year=,2012={'>=$(=MonthStart(Max(2012)))<=$(=Max(2012))'}>}BuyPriceRealized)

I dont know if that expression is correct if not i would also like to know how the "right" way is to do it...

Thanks ALOT!!

Re: Previous Month

Wed, 03 Oct 2012 16:22:12 GMT

I have only founded soulution where they use TODAY, -1 or -2 etc etc.. But i dont want it to be hardcoded As you can see my currentmonth is not hardcoded and changes every time i select a new month

Re: Previous Month

Wed, 03 Oct 2012 16:27:27 GMT

The only thing to note with the attached example is that the year may be confusing... For example, if you select Jan, the past month will be Dec, but it would be for the same year unless you specify otherwise.

Re: Previous Month

shree909 — Wed, 03 Oct 2012 16:36:36 GMT

Hi,

let us take example u have month field name( jan, feb, mar.......dec for year 2012

so u can create a variabe vmonth = Month

and in the expression

=Sum({<Month={ '$(vmonth)'}, Year=2012)'}>}BuyPriceRealized) this will show the month u have selected for year 2012

for previous year calculation i would suggest take the month number and convert into previous month number and then convert into month name in the expression..

Re: Previous Month

Wed, 03 Oct 2012 16:37:33 GMT

Thats okay cause i only have one year.

Re: Previous Month

Wed, 03 Oct 2012 16:39:39 GMT

But i already have current month i am in. I only need previous month

Re: Previous Month

Thu, 04 Oct 2012 10:59:15 GMT

Anybody else who have an suggestion?

Re: Previous Month

Thu, 04 Oct 2012 11:00:27 GMT

Your expression does not work e:/ But anyhow ived already made currentmonth Can u explain what u mean by converting? or give me an example - It should be previousmonth not previousyearmonth

Thanks in advanced!

Re: Previous Month

SunilChauhan — Thu, 04 Oct 2012 13:20:25 GMT

why Not Use MonthYear

vCurrMonth== Max(MonthYear)

vPrevMonth==Date(addmonths(Max(MonthYear),-1),'MMM-YYYY')

and In Label Left(vCurrMonth,3) and Left(vPrevMonth,3)

hope this helps

Re: Previous Month

Thu, 04 Oct 2012 13:22:59 GMT

So i create a variable called vCurrMonth and a variable called vPrevMonth

What do i write in my expressions then for my straight table? Just the Variable???

Re: Previous Month

SunilChauhan — Thu, 04 Oct 2012 13:25:50 GMT

no change required in expression

see below code

first expression

Sum({$<Month={"$(vCurrMonth)"}>}Volume)

second expression

Sum({$<Month={"$(vLastMonth)"}>}Volume)

third expression

=Column(1)-Column(2)

hope this helps

Re: Previous Month

Thu, 04 Oct 2012 13:27:59 GMT

What is monthYear ? Should i make a variable called month year also???

Re: Previous Month

SunilChauhan — Thu, 04 Oct 2012 13:45:48 GMT

its a field available in your application.

Re: Previous Month

Thu, 04 Oct 2012 13:46:36 GMT

Hmmm - i dont see it anywhere???

Re: Previous Month

Thu, 04 Oct 2012 13:47:26 GMT

IS it because i only have Personal edition ATM?

Re: Previous Month

SunilChauhan — Thu, 04 Oct 2012 14:08:24 GMT

if you have date field in script then make it as

Month(Date)&'-'&Year(Date) as MonthYear

Hope this helps

Re: Previous Month

Thu, 04 Oct 2012 14:09:39 GMT

I dont have any scripts - I made my DATE dimension in the data warehouse (ssis) - i have not created a MasterCalendar.... Do i need to do that?

Re: Previous Month

shree909 — Thu, 04 Oct 2012 19:59:10 GMT

so u have only month dimension? dont you have any dimenion related year, date, something like that...

Re: Previous Month

Fri, 05 Oct 2012 06:54:58 GMT

No i have a DateDimension

My Date dimension includes: Days, Month, Years, Quarter, Weeks and so on....

I have a row called Month_Name which includes january,feburary and so on... I try to use that one but it doesnt work?

Re: Previous Month

Fri, 05 Oct 2012 12:32:46 GMT

i cant it to work - i think its something about my timestamps ? Yours are in // - The strange think is when i make vCurrMonth i can see my months when selecting - but i cant see any sum...

If i make a listbox with the date as dimension and sum on my byprice it works perfectly!

Have i forgotten to change something somewhere???