topic Re: Previous month? in QlikView

Previous month?

Mon, 02 May 2016 11:18:28 GMT

Hi,

Is there any way to get the previous month out from this?

I have used for previous month: Year=Year(varMaxDate-7), value)

Whole script:

sum( {<[Campaign]={'1'}, [Value] = {">$(=0)<$(=100)"}>}

if(MonthNum=num(month(varMaxDate))-1 and Year=Year(varMaxDate-7),Value))

But now I have a case where I need previous month.. And I can not use -30, 'cause this is monthly data that is coming on one certain day on every month. Do I have to chop it and then again bring it back to date?

Cheers,

Niko

Re: Previous month?

sunny_talwar — Mon, 02 May 2016 11:22:56 GMT

May be like this:

Sum({<[Campaign]={'1'}, [Value] = {">$(=0)<$(=100)"}, Date = {"$(='>=' & Date(MonthStart(Max(Date), -1), 'YourDateFieldFormatHere) & '<=' & Date(MonthEnd(Max(Date), -1), 'YourDateFieldFormatHere))"}, Month, Year>} Value)

Re: Previous month?

Kushal_Chawda — Mon, 02 May 2016 11:26:01 GMT

if you have Date field in your model then you can use the below expression for Previous month. Make sure that Your Date should not be string, it should be in proper Date format

sum( {<[Campaign]={'1'}, [Value] = {">0<100"}, Date={">=$(=monthstart(addmonths(max(Date),-1))) <=$(=monthend(addmonths(max(Date),-1))) "}>} [Value])

Re: Previous month?

Mon, 02 May 2016 11:33:42 GMT

Thank you Sunny and Kushal for your help, actually this was what I was searching:

Year(addMonths(varMaxDate,-1))

Thank you for your posts, I did manage to do that!

Those expressions that you made, I did not get them to work for some reason. Maybe the problem is that I only have one certain date for each month.

Cheers,

Niko

Re: Previous month?

sunny_talwar — Mon, 02 May 2016 11:36:07 GMT

Regardless of how many dates you have in a month, the expression should have worked. Not sure about your data model, so can't really offer much.

Having said that, since you have already got it resolved, it would be helpful if you can close this thread by marking correct and helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny

Re: Previous month?

Kushal_Chawda — Mon, 02 May 2016 11:41:15 GMT

There is no such restriction of Number of Dates. may be Your Date is in string format, use date# to make it in Date format