Monthly Overlap - Max Data Point

[#1]

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

[sunny_talwar]

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

[#3]

Hi Sunny thanks,

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

[sunny_talwar]

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...

[#5 · Accepted solution]

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

[sunny_talwar]

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

[#7]

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

[#8]

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?

[sunny_talwar]

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

[#10]

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