Counting events on a growing population - help

[Hakim-A]

Hello Qlikcommunity,

I am requesting your help for a problem I can' t solve myself.

I have 2 sets of data

One containing vehicle information, dimensions are : vehicle_id assembly_date country (+ other descriptive variables)

One containing events information, dimensions are : vehicle_id event_date (+ other descriptive variables)

I would like to calculate per month the ratio : [number of events during the month] / [cumulative population so far]

I have no problem displaying these 2 calculations separately but when I try to create the ratio, I am facing 2 problems :

- How to deal with the dimension, how to create a dimension showing months from first assembly date to today ?

- How to simulate the cumulative population in qlikview.

The idea is to transform our data (see file enclosed) into something like this and display ratio per month

Would you have suggestions to achieve this ?

I've enclosed a data sample.

Thank you

[sunny_talwar]

Script

Table:

LOAD vehicle_id,

    assembly_date,

    country,

    Date(MonthStart(assembly_date, IterNo() - 1), 'YYYY-M') as Month

FROM

(ooxml, embedded labels, table is population)

While MonthStart(assembly_date, IterNo() - 1) <= MonthStart(Today());

Events:

LOAD vehicle_id,

    event_date,

    Date(MonthStart(event_date), 'YYYY-M') as Month

FROM

(ooxml, embedded labels, table is events);

Straight table

Dimension

Month

Expressions

=Count(DISTINCT vehicle_id)

=Count(DISTINCT event_date)

=Count(DISTINCT event_date)/Count(DISTINCT vehicle_id)

[Hakim-A]

Hello and thank you, the solution seems to work perfectly.

As i understood you have duplicated the vehicle_id and store them in YYYY-M

Is there a risk doing that with huge population (> 1 million) ?

Thank you

[Or]

QlikView stores each distinct value once, so storing the same value once per month shouldn't significantly impact performance compared to storing it once.

[sunny_talwar]

Huge application size and it might take extra time to reload... but can't think of other better ways to do this....