Last full month

[#1]

Hi,

I am new to QlikView so please excuse my ignorance. I have built report that looks at the sales revenue generated on a daily basis. The report run a day in arrears. For example when the report runs at the 6th July it will break down the data day by day for day 1 to day 5.

Obviously when we hit month end there is an issue as the formula I have built(sum({<[Sales Order Date]={">=$(=monthstart(today()))"} >} [Sales Ordered Value]) will only report for the current month. So on day 1 I want to report for the whole of the previous month.

This is pretty easy to do in crystal as I would us the lastfullmonth functionality and write an if then else statement. But as my QlikView knowledge is sparse I'm struggling.

Any help would be gratefully received

Thanks in advance

Dave

[sunny_talwar]

May be this:

If(Day(Today()) <> 1, Sum({<[Sales Order Date]={">=$(=MonthStart(Today()))"} >} [Sales Ordered Value]),

                              Sum({<[Sales Order Date]={">=$(=MonthStart(Today(), -1))"} >} [Sales Ordered Value]))

[MarcoWedel · Accepted solution]

Hi,

other solutions might be:

Sum({<[Sales Order Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} >} [Sales Ordered Value])

or, if there are no future Sales Order Dates, just:

Sum({<[Sales Order Date]={">=$(=MonthStart(Today()-1))"} >} [Sales Ordered Value])

hope this helps

regards

Marco

[#4]

Hi Sunny

Unfortunately this didn't work.

See the post from Marco below

[#5]

Marco,

This is perfect thanks so much it is greatly appreciated. One more thing I need to ask if that is ok?

Say I wanted to run if in the middle of the month for just last month how would I do it

[sunny_talwar]

Please Mark Marco's answer as correct if that's what the correct answer is. These threads are seen by people in future and you don't want to think that your response to Marco's correct answer is the correct answer.

Please read here:

Qlik Community Tip: Marking Replies as Correct or Helpful

[MarcoWedel]

maybe you can describe this new requirement in more detail in a new thread.

thanks

regards

Marco