
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Month End Values For Each Month
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.
- Subscribe by Topic:
-
Chart
-
Developers
-
dimension
-
expression
-
filter
-
General Question
-
Script
-
Set Analysis
-
Variables
-
Visualization

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have tired this its showing me value as 0 .
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
