Announcements
cancel
Showing results for
Did you mean:
Creator III

## Sum where Fieldname = Fieldname

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?

Labels (1)
• ### Qlik Sense

1 Solution

Accepted Solutions
Champion II

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

Sunil Chauhan
6 Replies
Champion II

in place of EndMonth, use monthend

Try this

monthend(MyDate) as ypA_EndMonth

and

SUM(\$<MyDate = {"\$(ypA_EndMonth)"}>}Sales)

Sunil Chauhan
Creator III
Author

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.

Creator III

Check this:

SUM({\$<MyDate = {"\$(=Max(ypA_MonthEnd))"}>} Sales)

If the issue is solved please mark the answer with Accept as Solution & like it.****

Champion II

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

Sunil Chauhan
Contributor III

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:
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