Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

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

  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

Tags (3)
21 Replies
Highlighted

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

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

Highlighted
Not applicable

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

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

Highlighted

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

Did this not work?

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

Highlighted
Partner
Partner

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

Maybe like this?

Capture.PNG

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)

Highlighted
Not applicable

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

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)

Highlighted

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

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

Highlighted
Not applicable

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

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.

Highlighted

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

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?

Highlighted
Partner
Partner

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

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