Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table with data for each day. But I only ever need the last day of the month. That's why I added the field "MonthEnd(MyDate) as ypA_MonthEnd" to the table.
Now I would like to always see the value of the last day of the month.
To do this I wanted to use the formula:
SUM({$<MyDate = {"=$(=ypA_MonthEnd)"}>}Sales)
But unfortunately I don't get any result. What did i do wrong?
try to use Number format of Date and Monthend
Num(Date) as Numdate
num(MonthENd(Date) as numMonthENd
or try to use in format using - dd-mm-yyyy
in place of EndMonth, use monthend
Try this
monthend(MyDate) as ypA_EndMonth
and
SUM($<MyDate = {"$(ypA_EndMonth)"}>}Sales)
Sorry, I made a mistake. I have it the way you wrote. However, unfortunately it doesn't work.
I corrected the first post above.
I get:
={}
No result found.
Check this:
SUM({$<MyDate = {"$(=Max(ypA_MonthEnd))"}>} Sales)
****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****
try to use Number format of Date and Monthend
Num(Date) as Numdate
num(MonthENd(Date) as numMonthENd
or try to use in format using - dd-mm-yyyy
Hi,
To only get the date (number) and not a decimal number you should use Floor(Num(MonthEnd(Date))) as Monthend_Num_Floor (change as needed)
You need to use the same format for any dates you want to do a match on or = to etc.
Data:
LOAD Date,
MonthEnd(Date) as Monthend,
Num(MonthEnd(Date)) as Monthend_Num,
Floor(Num(MonthEnd(Date))) as Monthend_Num_Floor
FROM
Date_Test.xlsx
(ooxml, embedded labels, table is Sheet1);
Regards
greenee
****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****
I suggest to create appropriate flags within the calendar, for example:
if(day(Date) = 1, -1, if(Date = floor(monthend(Date)), 1, 0)) as Flag
and then using the flag as dimension and/or selection and/or condition and/or by reducing the above to 0/1 as multiplicator and/or using dual() to add also a text to the chosen numbers.