problem with daily and accumulated events

[#1]

Hi all,

I've the following scenario I am not able to solve.

In my document I've a table activations that stores (unique_id, dateActivation, dealer) and a table first_activations that stores (another_unique_id, dateFirstActivation, dealer).

What I'd like to show on a chart is, for each day, the total # of activations made in a day divided by the unique dealers that since the beginning of the service made at least one activation.

If I make a count(unique_id)/count(dealer) I obtain the data of activations divide unique dealers of the day which is not what i need

Do you have any tip on how i can aggregate the unique dealers? I'd like to make something like count(unique_id)/count(dealers<since beginning>)

thanks,

g

[#2]

Use set analysis to overrule the time dimension. Something like

count{$<dateActivation =>} dealers)

[#3]

hi,

I worked on the formula you suggested but it doesn't work and I'm totally blind on how I can get closer to what I need

without any result.

I tried to add a new data field named dateActivation in first_activation table in order to create another association but it did not help me.

[#4]

Do you have an example qvw I can have a look at?

[#5]

attached a qvw.

the third chart is the one I want to change: on a daily basis it has to show the value of the first chart divided by the value of the second chart for the same day.

The first chart is made of the daily sum of the events from the table Eventi

The second chart is the progressive sum of the entries of the table PrimaAttivazione

The relation between the 2 tables is based on the dealer_id and the date.

The table PrimaAttivazione only stores the first entry of each dealer_id (contained into the table Eventi)

The table  Eventi contains alle the data generated by all the dealer_id

[#6]

Hi Gio,

I had a look at your qvw and tried a few things. My findings:

• Instead of flagging cumulative on the dealer-count expression, write your own cumul expression as RangeSum(Above(Count(dealer_id), 0, RowNo()))
• You do not need this PrimaAttivazi table. Even worse, it results in a synthetic key table which ruins your results when you bring dealer-count and event-count together.

If you could load me a qvw with only tables eventi and dealers, I'll try to put in the expressions I have here.

Cheers,

Ducati

[#7]

hi,

many thanks for your help so far. Attached the qvw with only the 2 tables.

g

[#8]

In your original qvw something is going wrong with the first activation date (unless I misunderstood what you meant with first activation date). I found a number of records where this is null although there is an activation event. Wondering whether this is the reason why your dealer count in the second chart is wrong.

[#9]

it's actually weird: i created a table and yes, I've seen there are multiple rows with no dataPrima field even if the values shown in the second chart, the total number of  unique dealers since the beginning, day by day, is correct.

Also, i run the mysql query that retrieves the data and it did not return any empty value

PrimaAttivazione:

SELECT

DATE_FORMAT(usim_activations.creation_timestamp,"%Y-%m-%d") as dataPrima,

date_format(usim_activations.creation_timestamp,"%Y-%m-%d") as DataEvento,

a.dealer as dealer_id,

a.dealer as dealerUnici

FROM usim_activations

INNER JOIN

(

SELECT

min(ua.id) as min_id,

dealer

FROM

usim_activations ua inner join dealers de on ua.dealer=de.id

where de.distributor<>5

GROUP BY dealer

) as a ON a.min_id=usim_activations.id

Order BY dataPrima asc;