topic Re: Line chart that works like Excel's countifs in QlikView

Line chart that works like Excel's countifs

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a table with three columns with one row per unique customer:-

months_since_start, cancelled_after and country_code

The first and third columns have no null values while the second will have a null value unless a customer left, in which case it holds a value indicating the number of months they were a customer before they left.

I want to produce a line chart where the expression is the retention rate with months in program (a vector that goes from 0 to max(months_since_start)) as the primary dimension and Country_code as a secondary dimensions.

A simplified mock-up excel version of what I'm trying to produce is attached.

Can anyone please guide me how I would script the expression to get the chart I get in excel using a series of Countifs()

I'm very new to Qlikview and hope I can do this within a chart expression maybe using RangeSum() ?

many thanks,

Michael

Re: Line chart that works like Excel's countifs

Kushal_Chawda — Sat, 15 Apr 2017 09:00:54 GMT

see this

Data:

LOAD months_since_start,

cancelled_after,

country_code

FROM

[retention sim.xlsx]

(ooxml, embedded labels, table is [simulated data]);

Left Join(Data)

LOAD cancelled_after as months_since_start,

count(cancelled_after) as Count_CancelAfter

Resident Data

Group by cancelled_after;

Left Join(Data)

LOAD cancelled_after as months_since_start,

country_code,

count(months_since_start) as Count_CancelAfter_Countrywise

Resident Data

Group by cancelled_after,country_code;

Left Join(Data)

LOAD months_since_start,

Count(months_since_start) as Count_MonthStart

Resident Data

where cancelled_after>=0

Group by months_since_start;

Left Join(Data)

LOAD months_since_start,

country_code,

count(months_since_start) as Count_MonthStart_Countrywise

Resident Data

where cancelled_after>=0

Group by months_since_start,country_code;

Left Join(Data)

LOAD months_since_start,

Count(months_since_start) as Count_Indivudual_MonthStart

Resident Data

Group by months_since_start;

Left Join(Data)

LOAD months_since_start,

country_code,

Count(months_since_start) as Count_Indivudual_MonthStart_Countrywise

Resident Data

Group by months_since_start,country_code;

Left Join(Data)

LOAD Count(months_since_start) as Count_OverAll_MonthStart

Resident Data;

Left Join(Data)

LOAD country_code,

Count(months_since_start) as Count_OverAll_MonthStart_Countrywise

Resident Data

Group by country_code;

Final:

NoConcatenate

LOAD *,

if(months_since_start=0,0,

if(previous(months_since_start)=0,Peek(Count_MonthStart),

if(months_since_start=Previous(months_since_start),Peek('Count_MonthStart_Increment'),

rangesum(Peek('Count_MonthStart_Increment'),Peek(Count_MonthStart))))) as Count_MonthStart_Increment,

if(months_since_start=0,0,

if(previous(months_since_start)=0,Peek(Count_MonthStart_Countrywise),

if(months_since_start=previous(months_since_start) and country_code=Previous(country_code),Peek('Count_MonthStart_Increment_Countywise'),

rangesum(Peek('Count_MonthStart_Increment_Countywise'),Peek(Count_MonthStart_Countrywise))))) as Count_MonthStart_Increment_Countywise,

if(months_since_start=0,Count_CancelAfter,

if(months_since_start=Previous(months_since_start),Peek('Count_CancelAfter_Increment'),

rangesum(Peek('Count_CancelAfter_Increment'),Count_CancelAfter))) as Count_CancelAfter_Increment,

if(months_since_start=0 ,Count_CancelAfter_Countrywise,

if(months_since_start=previous(months_since_start) and country_code=Previous(country_code),Peek('Count_CancelAfter_Increment_Countrywise'),

rangesum(Peek('Count_CancelAfter_Increment_Countrywise'),Count_CancelAfter_Countrywise))) as Count_CancelAfter_Increment_Countrywise,

if(months_since_start=0,Count_OverAll_MonthStart,

if(months_since_start=Previous(months_since_start),Peek('Count_Denominator'),

rangesum(Peek('Count_Denominator'),-Peek(Count_Indivudual_MonthStart)))) as Count_Denominator,

if(months_since_start=0,Count_OverAll_MonthStart_Countrywise,

if(months_since_start=previous(months_since_start) and country_code=Previous(country_code),Peek('Count_Denominator_Countrywise'),

rangesum(Peek('Count_Denominator_Countrywise'),-Peek(Count_Indivudual_MonthStart_Countrywise)))) as Count_Denominator_Countrywise

Resident Data

Order by country_code,months_since_start;

DROP Table Data;

Re: Line chart that works like Excel's countifs

Anonymous — Sat, 15 Apr 2017 16:41:51 GMT

Hi Kushal,

Many thanks - certainly more complex than I had anticipated but for sure calculates the correct metric.

Is a solution only possible via script? The reason I ask is because I have many fields in addition to country_code that I wish to use as the secondary dimension across multiple charts. I was hoping that an expression within a chart could work?

Effectively what I'm trying to calculate is as per the example below. In this example, month 6 retention = 1-(sum of green cells within red box/sum of green cells within green box) where the pivot is counts of cancelled_after.

Could we generate this within a chart by having months_since_start as the primary dimension and country_code or any column as the secondary dimension and somehow dynamically code the retention as a chart expression?

Clearly for each month of the primary dimension the cells to sum over differ for both numerator and denominator and the picture below shows what it would be for month 10. In the excel example I attached earlier the sum of the green cells within the red box were calculated by summing all the cells within the red box and subtracting off the sum of the yellow cells within the red box but there are other ways to get the same the same result.

Re: Line chart that works like Excel's countifs

Kushal_Chawda — Sat, 15 Apr 2017 18:45:34 GMT

Hi Michael,

I liked the way you explained by creating matrix in excel. But to calculate the numbers in excel matrix (which you have highlighted in green and red box) we need to perform some transformation based on the raw data which you shared in excel. This could be done in front end but I am not sure how. It is always better to do complex logic and calculation in script itself. I would try to implement it in front end but I am sure that would not be easy or may be my mind is not recognizing the easy way

Re: Line chart that works like Excel's countifs

Kushal_Chawda — Sat, 15 Apr 2017 18:47:07 GMT

If you have another fields in data you can share the sample of that as well, so that I can accordingly change the script

Re: Line chart that works like Excel's countifs

Anonymous — Sat, 15 Apr 2017 19:29:57 GMT

Truth is I have hundreds of other potential dimensions, this is why I was hoping for a front end solution so that I could rapidly explore potential dimensions to gain insights of factors that influence the retention curve. Thanks for you incredible help.

Re: Line chart that works like Excel's countifs

Kushal_Chawda — Sat, 15 Apr 2017 20:29:34 GMT

another approach

Data:

LOAD months_since_start,

cancelled_after,

autonumber(months_since_start&country_code) as Key,

country_code

FROM

(ooxml, embedded labels, table is [simulated data]);

Metrix:

LOAD * Inline [

Test ];

// Numerator where Month start is greater than equal to current month start and cancel after month (excluding blank) is less than equal to currrent month start

for i=1 to FieldValueCount('months_since_start');

let vMonth= FieldValue('months_since_start',$(i));

Concatenate(Metrix)

LOAD Count(cancelled_after) as Metrix,

country_code as Country,

AutoNumber('$(vMonth)' &country_code) as Key,

'$(vMonth)' as Month ,

'Numerator' as Flag

Resident Data

Where months_since_start>='$(vMonth)' and (cancelled_after>=0 and cancelled_after<='$(vMonth)')

Group by country_code;

NEXT i

let vMonth=Null();

// Denominator where Month start is greater than equal to current month start

for j=1 to FieldValueCount('months_since_start');

let vMonth= FieldValue('months_since_start',$(j));

Concatenate(Metrix)

LOAD Count(months_since_start) as Metrix,

country_code as Country,

AutoNumber('$(vMonth)' &country_code) as Key,

'$(vMonth)' as Month ,

'Denominator' as Flag

Resident Data

Where months_since_start>='$(vMonth)'

Group by country_code;

NEXT j

let vMonth=Null();

DROP Fields Test,Country,Month;

Re: Line chart that works like Excel's countifs

Gysbert_Wassenaar — Sun, 16 Apr 2017 16:29:41 GMT

Use a calculated dimension =valueloop(0,12) as first dimension, country_code as second dimension and this expression:

1-pick(ValueLoop(0,12)+1,

$(=concat( '(count({<cancelled_after={"<=' & ValueLoop(0,12) & '"}>}cancelled_after)-count({<months_since_start={"<' & ValueLoop(0,12) & '"},cancelled_after={"<' & ValueLoop(0,12) & '"}>}cancelled_after))/count({<months_since_start={">=' & ValueLoop(0,12) & '"}>}months_since_start)' ,',', ValueLoop(0,12)))

)

If you also want to show a Total line then on the Dimension Limits tab enable the Show Total option for the country_code dimension.

Re: Line chart that works like Excel's countifs

Anonymous — Sun, 16 Apr 2017 18:22:15 GMT

Gysbert,

Fantastic - works brilliantly - many thanks.

I was making some slow progress and had got this far:-

sum of green cells in green box (see an earlier visual post above) with this expression:-

Rangesum(below(sum({$} if(cancelled_after>=0 or len(trim(cancelled_after))=0,1,0)),0,50))

sum of yellow cells in red box with this expression:-

Rangesum(above(sum({$} if(cancelled_after>=0,1,0)),1,50))

but haven't yet figured out how to get the sum of green cells in the red box!