Month End Values For Each Month

[Rocky6]

Hi All,

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

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

Say if user selects May Month the bar chart should show value of may 31st and this should be applicable for all the months. 

Say for particular month we don't have 31st day so for that month it should show 30th value . 

My date field is MonthYear

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

Thanks in Advance. 

[MayilVahanan]

Hi

In that case, bring monthend flag in the script and use it in the set analysis. 

Load *, if(Date = MonthEnd(Date), 1, 0) as MonthEndFlag from ursource;

Sum({<MonthEndFlag = {1}>} [Final Amount])

[Rocky6]

I have tired this its showing me value as 0 . 

Thanks 

[NiTo]

Hi Rocky,

I tried this in load editor and work fine for me. Hope it will work for you too.

MonthEnd(Date) as LastDayOfMonth,
MonthStart(Date) as FirstDayOfMonth

Regards

[sidhiq91]

@Rocky6  Can you try the below code in your script:

NoConcatenate
Temp:
Load Monthend(Date(Date#(Date,'MMM-YYYY'),'MM-DD-YYYY')) as Date,
Sales
Inline [
Date, Sales
Jan-2023, 1000
Feb-2023, 2000
Mar-2023, 3000
Apr-2023, 4000
May-2023, 5000
];

Exit Script;

[Rocky6]

Hi Sidhiq91,

Thanks for your reply 

I have applied this logic but this shows total values of last day of that month but my requirement is that it should not show total value it should show only the value which is on the last day.

Eg)

Month,Date, Sales
Jan-2023, 1000
Feb-2023, 2000
Mar-2023, 3000
Apr-2023, 4000
May-2023, 5000
];

in this example we are showing 1/31/2023 values as 1000 but in that month all days have each value for measure so i need to show only that values. say the 31 jan date has 500 then it should show 500 not 1000.