Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Monthly Overlap - Max Data Point

Hi All,

Sometime ago, I opened a discussion regarding getting a maximum data point on  a line/bar chart.

I created three expressions one as a normal bars, one as a line and another one as a line the difference was that the third expression was only to look at the maximum date. The code I used was


if(Date=Max( {$< LinkId = {'SAL'} >}
Total Date),sum({< [Sales Order Date] = {">=$(=MonthStart(Today(),Day(Today())=1))"}, LinkId = {'SAL'} >}
total [Sales Ordered Value]))

This works perfectly on day 1. For example on the 1st December it will report for all of November, however, out system refreshes at 2.00am. One of my colleagues enters data at 1,00am on the 1st December. Hence this data is pulled through and the max date now is the 1st December and not the 30th November. So I need to exclude the December records

Any idea how I can do this

Thanks in advance

Dave

1 Solution

Accepted Solutions
Not applicable

Re: Monthly Overlap - Max Data Point

How would I need to amend the script for the data point today - day 12

View solution in original post

16 Replies

Re: Monthly Overlap - Max Data Point

Try this:

If(Date=Max( {$< LinkId = {'SAL'} >}

TOTAL Date),sum({< [Sales Order Date] = {">=$(=MonthStart(Today(1), Day(Today(1))=1))"}, LinkId = {'SAL'} >}

TOTAL [Sales Ordered Value]))

Not applicable

Re: Monthly Overlap - Max Data Point

Hi Sunny thanks,

As we are now on day 12 do I need to replace the red values of 1 with 12

Re: Monthly Overlap - Max Data Point

No you don't. Today() function uses 1 argument which helps it determine which today() to use. Reload() today, actual today() or document open today(). By default it is the reload today() and by adding 1 you make it actual today(). Look here

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/t...

Not applicable

Re: Monthly Overlap - Max Data Point

How would I need to amend the script for the data point today - day 12

View solution in original post

Re: Monthly Overlap - Max Data Point

You still want to see Nov's data in your expression?

Not applicable

Re: Monthly Overlap - Max Data Point

Yeah I get all November's data but I also have the data point against the 1st December. I need the data point against the 30th November and the 1st December excluded

Not applicable

Re: Monthly Overlap - Max Data Point

The pseudo code should be

if today is day 1 then then total of last months order or

if today is not day then total of current months orders.

Can that be transformed into Qlikview syntax?

Re: Monthly Overlap - Max Data Point

Is this not doing that?

If(Date = Max( {$< LinkId = {'SAL'} >}TOTAL Date),

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

From my understanding of the code, this will show you MTD Sales Ordered Value on the Max Date of this month if the date is any date except 1st of the month. If its the first of the month, then it will show you the MTD value for the last month on the max date.

Is this not what you want? If not then may be its time for you to share a small sample and explain with an example what exactly are you looking to get

Best,

Sunny

Not applicable

Re: Monthly Overlap - Max Data Point

This certainly works for the current month to date. However, I need to test that it works on the 1st day of the month for the last full month.

Can you suggest a way for me to test this?

Thanks in advance

Dave