topic Re: Monthly Overlap - Max Data Point in QlikView

Monthly Overlap - Max Data Point

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Monthly Overlap - Max Data Point

sunny_talwar — Mon, 12 Dec 2016 15:00:21 GMT

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

Re: Monthly Overlap - Max Data Point

Mon, 12 Dec 2016 15:11:26 GMT

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

sunny_talwar — Mon, 12 Dec 2016 15:13:58 GMT

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/today.htm

Re: Monthly Overlap - Max Data Point

Mon, 12 Dec 2016 15:42:41 GMT

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

Re: Monthly Overlap - Max Data Point

sunny_talwar — Mon, 12 Dec 2016 15:43:51 GMT

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

Re: Monthly Overlap - Max Data Point

Mon, 12 Dec 2016 15:54:51 GMT

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

Re: Monthly Overlap - Max Data Point

Mon, 12 Dec 2016 16:38:49 GMT

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

sunny_talwar — Tue, 13 Dec 2016 01:54:37 GMT

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

Re: Monthly Overlap - Max Data Point

Tue, 13 Dec 2016 09:34:09 GMT

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

Re: Monthly Overlap - Max Data Point

sunny_talwar — Tue, 13 Dec 2016 09:39:01 GMT

Replace Today(1) with Today(1)-12 which will force the expression to think that today is 1st and see if it sum's the last month or not

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

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

Re: Monthly Overlap - Max Data Point

Tue, 13 Dec 2016 10:10:35 GMT

no joy the line see the image

Re: Monthly Overlap - Max Data Point

sunny_talwar — Tue, 13 Dec 2016 10:11:28 GMT

What do you expect to see?

Re: Monthly Overlap - Max Data Point

Tue, 13 Dec 2016 10:17:13 GMT

the x axis should stop at 30/11/2016 and so should the line graph. The data point at 12/12/2016 should be at the 30/11/2016 and show £3,511,405

I can do a sample if you need to, can you tell me how

Re: Monthly Overlap - Max Data Point

sunny_talwar — Tue, 13 Dec 2016 10:19:05 GMT

Sample would be really helpful here

Re: Monthly Overlap - Max Data Point

Tue, 13 Dec 2016 10:22:26 GMT

can you please tell how I can produce one?

Re: Monthly Overlap - Max Data Point

sunny_talwar — Tue, 13 Dec 2016 10:24:07 GMT

See if these help

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample