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
TESTFO.qvw 255.8 K
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.
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.
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
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
min(ua.id) as min_id,
usim_activations ua inner join dealers de on ua.dealer=de.id
GROUP BY dealer
) as a ON a.min_id=usim_activations.id
Order BY dataPrima asc;