21 Replies Latest reply: Apr 4, 2017 10:27 AM by omar bensalem

# 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

• ###### 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

Value

FROM ....;

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

• ###### 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)

• ###### 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)

• ###### 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 Omar ben salem 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

• ###### 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.

• ###### 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?

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

Hi Sunny/Omar,

Thanks for your patience, I am a complete novice so this is hard for me.

I have a huge sales funnel of many different values ranged across lots of months in the format MMM-YY.

What I want to do is sum up all of the values for a defined date range, for example Jan-17 to Apr-17.

I think my example data set was very misleading as it only showed 1 value per month date.

In reality there would be hundreds of values in each month date.

It is the sum of the PWP (probability weighted pipeline) that I need to control the date for.

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

We completely understand where you are coming from, but here are the few things to check and understand. Is your Date a true date field?

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

If you fixed your date (or is already in date format), next step is to understand how set analysis work

Dates in Set Analysis

Once you have all this figured out, you will see how my and Omar's expression (one of the two based on the requirement) is exactly what you are looking for

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

Hi Sunny

If I am importing dates from excel that are not proper dates this could explain why I dont get good sorting behavior.

Is there a simple conversion in the import script?

Something to convert my excel MMM-YY into a Qlik recognised date?

Below you can see my attempt to filter these dates but I think Qlik is not treating them like dates.

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

Date(Date#(Month,'MMM-YY'),'DD/MM/YYYY').

Date#() will tell the QlikView the format of the given date, I mean how to read the date and the date() converts that in to the required format.

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

Hi,

Where would be the best place to put that in the load script?

Do I need to enter it for each LOAD?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

"Expected Comp. Opp." as "End date",

"Last Change",

"Start date",

"Opportunity ID",

"Quote ID",

"Customer Area" as "Sales Area",

"Market Segment" as "Market Sector",

"Org Division" as Division,

Prospect as Customer,

F10 as ERP,

"CRM Product category" as Product,

"Employee Responsible" as "Account Manager",

Phase,

"Phase Since",

Status,

"Status Since",

"Win probability (%)",

£ as "Opp value",

£1,

Region,

"End Month",

"Last Mod Month",

"Start Month",

"PWP"

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [P8C Funnel]);

"Fiscal year/period" as "Month",

"Sales document" as "Sales order_OR",

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F7 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Order value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Orders received]);

"Fiscal year/period" as "Month",

"Sales document" as "Sales order_OO",

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F7 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Open order value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Open orders]);

"Fiscal year/period" as "Month",

"Sales document" as Invoice,

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F8 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Invoice value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Revenue]);

"Account Manager",

"Orders Target",

"Revenue Target",

"Sales Region" as Region,

"Sales Area",

"42644",

"42675",

"42705",

"42736",

"42767",

"42795",

"42826",

"42856",

"42887",

"42917",

"42948",

"42979"

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, table is [Individual targets]);

"Goal month",

"Industry goal",

"Industry actual",

Service

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, table is [Month targets]);

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

"Expected Comp. Opp." as "End date",

"Last Change",

"Start date",

"Opportunity ID",

"Quote ID",

"Customer Area" as "Sales Area",

"Market Segment" as "Market Sector",

"Org Division" as Division,

Prospect as Customer,

F10 as ERP,

"CRM Product category" as Product,

"Employee Responsible" as "Account Manager",

Phase,

"Phase Since",

Status,

"Status Since",

"Win probability (%)",

£ as "Opp value",

£1,

Region,

"End Month",

"Last Mod Month",

"Start Month",

"PWP"

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [P8C Funnel]);

Date(date#(Month,'MMM-YY'),'MMM-YY') as FinalMonth;

"Fiscal year/period" as "Month",

"Sales document" as "Sales order_OR",

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F7 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Order value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Orders received]);

DROP FIELD Month;

Date(date#(Month,'MMM-YY'),'MMM-YY') as FinalMonth;

"Fiscal year/period" as "Month",

"Sales document" as "Sales order_OO",

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F7 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Open order value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Open orders]);

DROP FIELD Month;

Date(date#(Month,'MMM-YY'),'MMM-YY') as FinalMonth;

"Fiscal year/period" as "Month",

"Sales document" as Invoice,

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F8 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Invoice value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Revenue]);

DROP FIELD Month;

"Account Manager",

"Orders Target",

"Revenue Target",

"Sales Region" as Region,

"Sales Area",

"42644",

"42675",

"42705",

"42736",

"42767",

"42795",

"42826",

"42856",

"42887",

"42917",

"42948",

"42979"

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, table is [Individual targets]);

"Goal month",

"Industry goal",

"Industry actual",

Service

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, table is [Month targets]);

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

The links I shared showed you exactly what you need to do. besides Oman and Shiva have also shown what needs to be done

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

Suppose your date field in excel is Jan-17 but it's not really a date that Qlik is recognizing:

Here is how your proceed :

Date(date#(Month,'MMM-YY'),'MMM-YY')

Let's break this to understand it:

1) We know that our field is formatted as follow : 'MMM-YY'; so we want to tell Qlik to believe us, and treat it like a date (a sort of cast):

so what we do is the follow:

Date#(Month,'MMM-YY') : this is like telling Qlik to approach this field like a date and to tell it's formatted like that.

But, in this step, we havn't formatted it yet.

2) Next step is to format it using the Date function (Function with # are for 'casting' without # are for formating)

So what we do: is format our cast "Date#(Month,'MMM-YY') " with Date and entering the new format:

Date(Date#(Month,'MMM-YY') ,'MMM-YY')

Clear enough?

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

Let's suppose we have this:

Month1,    Value1, Value2, Value3

Jan-17,    2400,    300, 500

Feb-17,    500,400,600

Mar-17,    12000,8000,300

Apr-17 ,    20,100,500

May-17,    4000,600,50

Jun-17,    1000,300,40

];

So we have 3 measures: sum(Value1), sum(Value2) and sum(Value3)

Now, if we choose a bar chart:

As dimension: Month

As measures:

1)sum(

As dimension: Month

As measures:

1)sum(Value1)

2)sum(Value2)

3)sum(Value3)

Value1)

2)sum(Value2)

3)sum(Value3)

We would have this:

Which is sum(Value1)+Sum(Value2)+Sum(Value3) by month:

Now we want to limit the months and have the sum only from month >=Jan-17 and <=Apr-17:

We need to alter our expressions:

As dimension: Month

As measures:

1)sum({<Month={"<=Apr-17 >=Jan-17"}>}Value1)

2)sum({<Month={"<=Apr-17 >=Jan-17"}>}Value2)

3)sum({<Month={"<=Apr-17 >=Jan-17"}>}Value3)

We will have :

Now: we want to cumulate these measures; we do not need the sum of vale1,2 and 3 by month; but we want them to be cumulative.

So we alter each expression:

1)Rangesum(above( sum({<Month={"<=Apr-17 >=Jan-17"}>}Value1),0,rowno()))

2)Rangesum(above( sum({<Month={"<=Apr-17 >=Jan-17"}>}Value2),0,rowno()))

3)Rangesum(above( sum({<Month={"<=Apr-17 >=Jan-17"}>}Value3),0,rowno()))

Result:

Please see the attached app (second sheet)

And refer to this thread to understand more the set analysis:

YTD, MTD issue

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

Hi Sunny

I'd want to see today's Month up to and including a defined cut off Month like Jun-17.

It is the kind of function the date slicer in Excel can do.

Thanks!

• ###### 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

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

Maybe like this?

In the script:

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)

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

Try this.

Just add a flag if the month is <= flag and use that in expression

load *,if(Month(Date#(Month,'MMM-YY'))<=4,1,0) as Flag inline [

Month,Value

Jan-17,2400

Feb-17,500

Mar-17,12000

Apr-17,20

May-17,4000

Jun-17,1000];

Below is the expression

=sum({<Flag={1}>}Value)

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

May be like this

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

"Expected Comp. Opp." as "End date",

"Last Change",

"Start date",

"Opportunity ID",

"Quote ID",

"Customer Area" as "Sales Area",

"Market Segment" as "Market Sector",

"Org Division" as Division,

Prospect as Customer,

F10 as ERP,

"CRM Product category" as Product,

"Employee Responsible" as "Account Manager",

Phase,

"Phase Since",

Status,

"Status Since",

"Win probability (%)",

£ as "Opp value",

£1,

Region,

"End Month",

"Last Mod Month",

"Start Month",

"PWP"

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [P8C Funnel]);

Date(Date#("Fiscal year/period", 'MMM-YY'), 'MMM-YY') as "Month",

"Sales document" as "Sales order_OR",

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F7 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Order value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Orders received]);

Date(Date#("Fiscal year/period", 'MMM-YY'), 'MMM-YY') as "Month",

"Sales document" as "Sales order_OO",

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F7 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Open order value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Open orders]);

Date(Date#("Fiscal year/period", 'MMM-YY'), 'MMM-YY') as "Month",

"Sales document" as Invoice,

"Customer group 3" as "Sales Area",

"Customer group 1" as "Market Sector",

Division,

"(AG) Sold-To Party" as Customer,

F8 as ERP,

"(ZA) Sales Rep." as "Account Manager",

£ as "Invoice value",

Region

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [BW Revenue]);

"Account Manager",

"Orders Target",

"Revenue Target",

"Sales Region" as Region,

"Sales Area",

"42644",

"42675",

"42705",

"42736",

"42767",

"42795",

"42826",

"42856",

"42887",

"42917",

"42948",

"42979"

FROM [lib://General Dashboard/Dashboard Workbook.xlsm]

(ooxml, embedded labels, table is [Individual targets]);

"Goal month",