Line chart that works like Excel's countifs

[Anonymous]

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

[Kushal_Chawda · Accepted solution]

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;

[Anonymous]

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.

[Kushal_Chawda]

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

[Kushal_Chawda]

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

[Anonymous]

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.

[Kushal_Chawda]

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;

[Gysbert_Wassenaar]

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.

[Anonymous]

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!