topic Re: Last full month in QlikView

Last full month

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Last full month

sunny_talwar — Thu, 16 Jun 2016 16:00:05 GMT

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]))

Re: Last full month

MarcoWedel — Thu, 16 Jun 2016 21:29:18 GMT

Hi,

Re: Last full month

Fri, 17 Jun 2016 11:01:45 GMT

Hi Sunny

Unfortunately this didn't work.

See the post from Marco below

Re: Last full month

Fri, 17 Jun 2016 11:08:06 GMT

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

Re: Last full month

sunny_talwar — Fri, 17 Jun 2016 11:44:25 GMT

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

Re: Last full month

MarcoWedel — Sat, 18 Jun 2016 22:17:21 GMT

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

thanks

regards

Marco