Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Hope all are doing good...
I need your help to solve one of the issue that i am facing.
I have a date field in DD/MM/YYYY and i have retrieved Month and Year from that field.
Now the issue is that when i am writing the Max(Month) expression to select Maximum month it is returning Month number instead of Month Name.As a result the value is not getting selected in the month field.
However I have used Month(Max(Date)) to get maximum month and i am able to select maximum month with that expression.
But at the same time for YTD calculation i have used Sum({<Year={"$(=Max(Year))"},Month={"$(=Month(Max(Date)))"}>}Sales) which returning '0'.
Please help me with the correct approach to resolve the issue.
Thanks in advance.
Best Regards,
Nani
It seems you are looking for a YTD expression and not MTD. For YTD, try this:
Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'DD/MM/YYYY'))"}Year = ,Month = >}Sales)
try this
= Sum({<Year={'$=Max(Year)'},Month={'$(=Month(Max(Date)))'}>}Sales)
Why don't you do this with the dates instead?
Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'DD/MM/YYYY'))"}Year = ,Month = >}Sales)
Hi Suri,
Thanks for response.
I have used Sum({<Year={"$(=Max(Year))"},Month={"<=$(=Month(Max(Date)))"}>}Sales) expression. But no luck it is returning 0.
Hi Sunny,
Thanks for the response. But we dont have Date filters on the dashboard.
You don't need to have date filters. The expression will still work based on selections you make in Year and Month fields. Try it out
Try like this at script?
Year*12+Month(Date) as MonthNumer
And then this expression:
=Sum({<Month={'$(=Max(MonthNumer))'} }>}Sales)
As you said:
Now the issue is that when i am writing the Max(Month) expression to select Maximum month it is returning Month number instead of Month Name.
When you use Max(Month), max() returns integer only, will not return month name
Sure sunny i will try. But my question is we have ignored the selections in Yearanf month in the expression right.
Do you still think that this expression will work out?
Yeah i realized and used Month(Max(Date)) to get the Max month. But that is not working as expected when i am using for YTD with the below expression
Sum({<Year={"$(=Max(Year))"},Month={"<=$(=Month(Max(Date)))"}>}Sales)