Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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])
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
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
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