
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How do I sum a range values from a subset of months from within the range?
Hi
If I have a sales funnel table with two columns
- Month
- Value
And data organised like this:
Month Value
Jan-17 £2400
Feb-17 £500
Mar-17 £12000
Apr-17 £20
May-17 £4000
Jun-17 £1000
What expression do I use to limit the sum value to only Jan-17 to Apr-17?
Thank you
- « Previous Replies
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be like this
Sum({<Month = {"$(='>=' & Date(YearStart(Today()), 'MMM-YY') & '<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Value)
Where Month field is created like this in the script
LOAD Date(MonthStart(TempDate), 'MMM-YY') as Month,
Value
FROM ....;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny
I already have my months in the correct format (MMM-YY).
Is there a simple expression to do this? Something like:
sum([value],if(date=Jan-17:Apr-17) ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Did this not work?
Sum({<Month = {"$(='>=' & Date(YearStart(Today()), 'MMM-YY') & '<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Value)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe like this?
In the script:
load *, Date(date#(Month1,'MMM-YY'),'MMM-YY') as Month;
load * Inline [
Month1, Value
Jan-17, 2400
Feb-17, 500
Mar-17, 12000
Apr-17 , 20
May-17, 4000
Jun-17, 1000
];
And as a measure in your chart :
Sum({<Month={"<=Apr-17 >=Jan-17"}>}Value)
But the expression of Sunny is a dynamic one:
Sum({<Month = {"$(='>=' & Date(YearStart(Today()), 'MMM-YY') & '<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Value)
It will always show the sum of value for the first month of the year (jan) to the actual month (the month of today) which is April.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I want Jan-17 to Apr-17 then do I do this:
Sum({<Month = {"$(='>=' & Date(YearStart(Today()), 'JAN-17') & '<=' & Date(MonthStart(Today()), 'APR-17))"}>}Value)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you always want Jan-17 and Apr-17 and this will never change in 1 month from now, then use what omarbensalem has shared.
UPDATE: I was just giving you a dynamic expression which would change each month. If we are in May, the expression would have given you Jan-17 till May-17. If this is not what you want, then you can use static values as give by Omar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, for some reason this does not work.
It feels like there should be a much simpler way to do this.
In excel I would just use a sumif function.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set analysis, may not be the simplest, but is the most efficient way to do this.... What is your exact requirement? If we are in the month Aug 2017, would you still want to see Jan-17 to Apr-17? or would this change to Jan-17 to Aug-17?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
We already provided you the simple expression;
But as we said, the expression provided by Sunny is a dynamic one that will show your KPI from the start of the year till the current month

- « Previous Replies
- Next Replies »