- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sum to latest month
Hi
I have a field named month which give me data in a dd/mm/yyyy format. I then have a target column I want to just sum to latest month. i.e. if it is November there will be data for the full year but I want it to just show up to November.
One other thing is that I don't want this figure to change no matter what selection is made by the user from the various Listboxes I have.
Thanks
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you Don't have YearStart Field then
sum({1<CalendarDate = {">=$(=Date(YearStart(Max(CalendarDate))))<=$(=Date(Max(CalendarDate)))"}>}Target)
else
sum({1<CalendarDate = {">=$(=Date(Max(YearStart)))<=$(=Date(Max(CalendarDate)))"}>}Target)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Why can't simple set operator
And you should have month field from script
Month(Datefield) as Month
And then try to use this from your expression.
Sum({<Month = {'$(=Max(Month))'}>}Sales)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Both the above expressions return me the total year value and not up to the latest month.
I don't have a year start value. I do have a year field but it is not a date format and just a string yyyy
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Anil,
Thanks for the help
This only returns me the latest month. I want a sum of all month's up to the latest.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Then use something like this
Sum({<Month = {>=Month(YearStart(Today())) <= Max(Month)}>}Sales)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use the below in Master Calendar script
If( Date > monthstart(addmonths(today(),-10)) and Date <= today(),1) | as [Rolling 11], |
And in your expression use
=Sum({<[Rolling 11]={1}, your dimension/measure>}Sales)
Rolling 11 will only give you data only until Nov-2016.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Kwok,
First convert that date into month like :- Date(DateField,'YYYY-MM')
now raise a flag like if(interval(date(today(),'YYYY-MM'),Date(DateField,'YYYY-MM'))=0,'CurrnetMonth','PreviousMonth') As MonthStatus
Now u can use this flag in your expression by giving filter MonthStatus='CurrentMonth' .
Thanks,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Expression will give you values from year start to till date
If max date in your calendar data is 21st November then that expression will give you data from 1st jan till 21st November.
When you say upto November what does it actually mean?
Also check what is max date in your calendar?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If your calendar has future dates,then it will consider those dates also while calculating max date.
- « Previous Replies
-
- 1
- 2
- Next Replies »