reference line of dimension does not work with grouped week x axis at line chart

[coloful_architect]

I want to use makedate expression to create a reference line at line chart.

when it is calendar day x axis, it shows good like this .

however, it does not work at x axis when I convert calendar day into week

It took a bit time to realize that only when I turn "continuous" on, the reference comes back. but my week axis is gone.

I use week function to convert the calendar day into week.

is there a way I can show the x axis with week label ( like the second label) but also have the reference line ? 

thanks 

[Javizh · Accepted solution]

Hello.

When you create a Dimension refference line, your "Dimension value" expresion should always return a value existing in the X axis.

With the Date axis, using a "Max(Date)"  or "Today()"  function should work.

But when you use the Week axis, you need an expression that returns a week value. For example "Only({<Date = {'$(=Date(Today()))'}>} Week)".

You cannot use the same expression for both axis unless its a continious axis, which you dont want.

PS: I used the "Today()" funcion, but you can replace it with the date you want.

Attached is an example.

Hope its helpful.

Best Regards.

[coloful_architect]

Javizh, it works like a charm!  Appreciate it.

I have a follow-up question ,hoping you could help me again.

now everything is grouped by week, at that chart of weekly axis, how can I only show the value as the last day of week rather than the sum aggregation of all days within single week ?

for example, the data table I have is 

day, week, sales

1         1          10

2         1           15

3         1            16

at line chart, I only want to the value label to show the day 3 's value of 16  rather than  10+15+16 ?

please advise.

[Javizh]

Hello coloful_architect.

Im happy it was helpful! 🙂

Regarding your second question, there is some options, but here is what I would do:

Having the week I want to represent, I would transform it into a variable:

• vCurrentWeek  = "=Only({<Date = {'$(=Date(Today()))'}>} Week)"

Then, in the "Label" expression from the reference line, you need to sum your Sales field filtered by the max Date of that Week, which equals vCurrentWeek 

Transforming that to Set Analisys: 

• sum({< Date = {'$(=Max({<Week = {'$(vCurrentWeek)'}>}Date))'}>}Sales)

Then you mark "Show label" and unmark "Show value" and that should show your expression with the value you are looking for.

Note that the "Dimension value" is unchanged but you can use your variable now that you created it.

Attached is the updated example with another sheet.

Hope its helpful.

Best regards.

[coloful_architect]

much appreciated your prompt response. Javizh. you are way ahead of game..

actually I was looking for the measurement expression to show the last day value rather than reference line.

for example , at your qvf, I want each week of the line to show the value of that last day in the week.

if you check the screenshot, I want 2023/31 to show 19,575 which is the value of day 6/8/23 rather than the sum aggregation of 112,617... what I should change at measurement expression? 

sorry I should be more specific. 

[Javizh · Accepted solution]

Hello coloful_architect.

I see, I didnt catch you!

Then you need to do your Sales sum, only when the Date equals the last Date of each Week.

Transforming that to Set Analisys: 

• sum( {< Date = {"=Date = Aggr(NODISTINCT Max(Date),Week)"}>} Sales)

Attached is the updated example with another sheet.

Hope I understood correctly this time.

Best Regards