Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use set analysis to overrule the time dimension. Something like
count{$<dateActivation =>} dealers)
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.
Do you have an example qvw I can have a look at?
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
Hi Gio,
I had a look at your qvw and tried a few things. My findings:
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
hi,
many thanks for your help so far. Attached the qvw with only the 2 tables.
g
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
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;