Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
reporting_neu
Creator III
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)
1 Solution

Accepted Solutions
SunilChauhan
Champion II
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

View solution in original post

6 Replies
SunilChauhan
Champion II
Champion II

in place of EndMonth, use monthend

Try this

monthend(MyDate) as ypA_EndMonth

and 

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

Sunil Chauhan
reporting_neu
Creator III
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.

TauseefKhan
Creator III
Creator III

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.****

SunilChauhan
Champion II
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
greenee
Contributor III
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:
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);



greenee_0-1717756129257.png


Regards
greenee

****Hope this resolve your issue.

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

marcus_sommer

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.