Value For Each Month Last Date

[Rocky6]

Hi All,

I have requirement  i am using set as sum(final Amount) to show values for all month in a table.

But my requirement is in that table i should show values of each month last date.

Say if user selects march field it should display total values of 31st March  end value not the whole month value and should be same for all months. 

My date field is Date.

I tired with MonthEnd function but i am not getting the values.

[marcus_sommer]

LAST_UPDATED_DATE  is a normal calendar-date or a working-day date or an existing fact-table related date or anything else? 

[Rocky6]

LAST_UPDATED_DATE   is normal field date coming from database 

[marcus_sommer]

LAST_UPDATED_DATE  is a normal calendar-date or a working-day date or an existing fact-table related date or anything else? 

[Rocky6]

 normal calendar-date

[marcus_sommer]

If it has a calendar-date relation the above mentioned query against monthend() will work. If it's not worked the kind of relation is another one - which one? 

[Rocky6]

I am getting month end value , but for some months 31st  value is not available , so for that month i need to show values which is there for 30 . 

March 31/03/23 i dont have values so i need to show which is there in 30th march like that for all months 

[marcus_sommer]

It's not really an answer to the kind of date-relation. Nevertheless I will take it as implicit hint for a fact-table relation. So you may do something like:

m: mapping load monthname(YourDateField), max(YourDateField)
from X group by monthname(YourDateField);

t: load *, -(YourDateField=applymap('m', monthname(YourDateField), 0)) as Flag
from X;

[Rocky6]

I dont know how to explain you in this community. If your ok can you ping me your mobile number in personnel message so that i can explain you .

[marcus_sommer]

Moving from the community into personnel communications for helping by normal questions goes in the wrong direction because no one could participate anymore - neither others to give further hints nor others who could learn/deducting anything from the existing content. Further the community could not do the work of others else it's aimed to provide possible approaches or directions and giving hints and help to self-help.

Did you really tried the various mentioned suggestions? It's not so complicated and wouldn't require much time and efforts - especially if you creates a small dummy-application with just two or three small inline-tables with a few fields and records each - maybe even faster made in Excel - and then adapting the provided suggestions step by step. You will get immediately the results and could play with various approaches and doing some trial & error. If you could comprehend the functions/logic you could increase the complexity step by step and after a while you could transfer it within your origin application. 

[Chanty4u]

Hi you can try this out

Sum({<Date={$(=MonthEnd(Max(Date)))}>} Amount)