topic Re: combo chart challenge in QlikView

combo chart challenge

Fri, 19 Jun 2015 21:09:19 GMT

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

];

Re: combo chart challenge

hic — Tue, 23 Jun 2015 12:30:32 GMT

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

Re: combo chart challenge

Tue, 23 Jun 2015 13:57:47 GMT

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

Re: combo chart challenge

Wed, 24 Jun 2015 12:47:00 GMT

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

Re: combo chart challenge

hic — Wed, 24 Jun 2015 19:19:20 GMT

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

Re: combo chart challenge

Thu, 25 Jun 2015 08:29:26 GMT

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

Re: combo chart challenge

hic — Thu, 25 Jun 2015 09:13:05 GMT

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

Re: combo chart challenge

Thu, 25 Jun 2015 11:30:26 GMT

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