How do I sum a range values from a subset of months from within the range?

[#1]

Hi

If I have a sales funnel table with two columns

1. Month
2. 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

[sunny_talwar]

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 ....;

[#3]

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)  ?

[sunny_talwar]

Did this not work?

Sum({<Month = {"$(='>=' & Date(YearStart(Today()), 'MMM-YY') & '<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Value)

[OmarBenSalem]

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.

So, if we're in 05/05/2017; no need to go back and change your expression, It will adapt itself and sum the values from jan to Mai(month of 05/05/2017)

[#6]

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)

[sunny_talwar]

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

[#8]

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.

[sunny_talwar]

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?

[OmarBenSalem]

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