combo chart challenge

[#1]

Hi,

I'm new to Qlik and I can't figure out how to create a combo chart that show accumulative ACV by month (bars), together with YTD quota (line) for selected Team Member in Qlik Sense.

I think that my challenge is that opportunities table have a CloseDate, and quotas table have another quoteMonth date, but I couldn't solve this. Hopefully I'm missing something simple. Thanks in advance.

Cheers,

Pablo

The model:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

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

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

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

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

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

Quotas:

Load * inline [

  Name, quotaMonth, quota

    Peter, Jan, 300

    Peter, Feb, 200

    Peter, Mar, 154

    John, Jan, 300

    John, Feb, 200

    John, Mar, 154

    Sam, Jan, 300

    Sam, Feb, 200

    Sam, Mar, 154

];

OpportunityTeamMember:

Load * inline [

  Name, OppId, Role

    Peter, 1, Primary

    Peter, 2, Primary

    Peter, 3, Primary

    John, 4, Primary

    John, 5, Primary

];

Opportunities:

Load * inline [

  OppId, OppName, CloseDate

    1, opp1, Jan

    2, opp2, Jan

    3, opp3, Mar

    4, opp4, Feb

    5, opp5, Mar

];

[hic]

There are a couple of ways to do this, but all of them include linking CloseDate with quotaMonth. I would do the following: (I added a field with opportunity value)

// =============== Opportunities
Opportunities:
Load OppId, OppName, CloseDate, Value Inline

[OppId, OppName, CloseDate, Value
...  ];

OppId2Month:
Mapping Load OppId, CloseDate Resident Opportunities;

// =============== Quotas
Quotas:
Load * inline

[Name, Month, quota
... ];

// =============== Team members
OpportunityTeamMember:
Load Name, OppId, Role,
     ApplyMap('OppId2Month', OppId, Null()) as Month
Inline

[Name, OppId, Role
... ];

Then you will get a data model like below and can make a simple chart with accumulation.

HIC

[#3]

Hi Henric,

Thank you for taking the time to look into this. Changing the model as you suggested and calculating the accumulative value and quota using sum(aggr(rangesum(above(total sum({<Month=>}Value),0,RowNo())),Month)), got me the result that I was looking for (I couldn't find in Qlik Sense the accumulative option that you have in QlikView).

Just for future reference, i used the following post to understand how to get the accumulated value

Calculating rolling n-period totals, averages or other aggregations

Thank you,

Pablo

[#4]

Hi Henrik,

I'm having a related challenge with this chart. I have one serie for Won opportunities, one for open opportunities and one for quota (I added a status field in opportunity table). Each serie is accumulative, and the resulting chart is the following:

What I'm looking for is to have in Apr, a bar for Won that's equal to Mar because it's accumulative.

I'm sure that the challenge is with the formula that I'm using to calculate the accumulative value, but not sure how to fill missing values.

Any ideas?

Thank you,

Pablo

[hic]

This is possible. In the chart below you can see the formulas in the legend. The accumulation is made with the built-in functionality (the radio button on the Expressions sheet).

HIC

[#6]

Hi Henric,

Where can I find the accumulation radio button built-in functionality in Qlik Sense?

I kind of solve the problem by changing the formula to: rangesum(above(total sum({<[Month]=,Status={'Won'},[Month.Year]={2015}>}[Value]),0,RowNo())).

I said Kind of, because it doesn't work if I only select specific months of 2015 (for example Apr-Jun), which it's fine for my use case because I want the full year. It would be useful to tell the measure expressions to ignore any filter by Month (only Month filter, because I do want to have the AE filter). Is there a way to do that?

Thank you,

Pablo

[hic · Accepted solution]

There is no accumulation button (yet) in Qlik Sense, so you need to use an expression like the one you've written.

About the expression: It should work, and when I try it on my mock-up data, it also does. So I don't understand what you mean when you say that it doesn't work. The "...<Month= ,..." will effectively remove any selection in the Month field, so that the measure will be calculated on all months.

HIC

[#8]

Hi Henric,

You are right. It does work. I check all the formulas, and the problem was that I had some measures with aggr and some without it.

Thank you for your support!

Pablo