Setting a Target Line depending on date

[Ariffk]

Hi all,
I'm new to Qlik.
I need to create a target line / Forecast line depending on the date.

Certain weeks we forecast a certain intake of orders.

I have a Bar graph which displays the total number of orders which came in each week (weeks start on Monday).

How can I create a forecast line on this graph showing how many were expecting in previous weeks and how many we're expecting in future weeks?

I can get a flat reference line on the chart but I can't get it to change depending on the week.

For example I want to change week ending 4th June 2023 (I will be adding additional dates too)

I've tried this IF statement but it's not working properly - It's only showing my reference line at 1555 (600 & 1555 are just test figures)

=if(weekend(OrderReceivedDATE,0,0)=date(04-06-2023,'DD-MM-YYYY'),600,1555)

Where am I going wrong?
Thanks.

[Javizh]

Hello @Ariffk 

The weekend function returns the last milisecond of the last day of that week. that means that you are comparing the day '04/06/2023' (a whole number) with a decimal number "weekend(OrderReceivedDATE,0,0)".

I suggest you use the Floor function to get a whole number from the weekend function too.

Also, in the date function, you should use the single quotes to get your desired date, because if you dont do it,qlik will interpret the '-' as a minus.

Be carefull too with the format of the date you manually define, as it has to be the same as its defined in your qlik  (for example, in my case it is 'DD/MM/YYYY').

In summary, this is the way your function should be:

• =if(Floor(weekend(OrderReceivedDATE,0,0))=Date('04/06/2023','DD/MM/YYYY'),600,1555)

The date function is a formating function. Its not needed to compare two whole numbers, so you can simplify

• =if(Floor(weekend(OrderReceivedDATE,0,0))='04/06/2023',600,1555)

Hope its helpful.

Best regards.