topic Counting events on a growing population - help in QlikView

Counting events on a growing population - help

Hakim-A — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Counting events on a growing population - help

sunny_talwar — Tue, 22 Aug 2017 11:29:28 GMT

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)

Re: Counting events on a growing population - help

Hakim-A — Tue, 22 Aug 2017 13:43:24 GMT

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

Re: Counting events on a growing population - help

Or — Tue, 22 Aug 2017 13:49:45 GMT

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

Re: Counting events on a growing population - help

sunny_talwar — Tue, 22 Aug 2017 14:01:31 GMT

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