Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I try to calculate a revenue in a chart, but the logic makes it hard to implement:
= Sum( {<Traff_Campaigns.PricingModel={'dCPC'}>} cost)*Traff_Campaigns.Margin
The idea is, for example, if it is about PricingModel "dCPC", we need to sum the "cost" and then multiply it by Margin.
But when i use this calculation, it not works well for all dimension in the chart (see attatched .qvw) .
I guess the expression does not know how to deal with the multiply of the Margin, since it could be many margins for the same PricingModel.
Thanks,
Guy
Guy,
I think you are right, that's almost for sure one issue. Another one might be that it seems that you don't have Margins set for every cost line.
I assume you could probably get a better result if you are using an advanced aggregation using an appropriate dimension (on finest level of details), since you are also using a group dimension, maybe like
=sum(aggr( Sum( {<Traff_Campaigns.PricingModel={'dCPC'}>} cost)*Traff_Campaigns.Margin,Traff_Plat_Key))
as expression in your chart.
Still some lines get zero revenue, because there seems to be no Margin value linked.
Regards,
Stefan
Hi guy,
So if there are multiple margins, what do you want to do? Choose one? Multiply by an average of them all? Add them together before multiplying?
Jason
Guy,
I think you are right, that's almost for sure one issue. Another one might be that it seems that you don't have Margins set for every cost line.
I assume you could probably get a better result if you are using an advanced aggregation using an appropriate dimension (on finest level of details), since you are also using a group dimension, maybe like
=sum(aggr( Sum( {<Traff_Campaigns.PricingModel={'dCPC'}>} cost)*Traff_Campaigns.Margin,Traff_Plat_Key))
as expression in your chart.
Still some lines get zero revenue, because there seems to be no Margin value linked.
Regards,
Stefan
Are the result correct if you change the expression to:
= Sum( {<Traff_Campaigns.PricingModel={'dCPC'}>} DISTINCT cost*Traff_Campaigns.Margin)
?
And your margins are probably given in Percent, so you need to divide by 100.
Hi Swuehl,
Yes, your idea led me to the solution.
Indeed defining the problem is the hardest part for the suolution:
=sum(aggr(Sum( {<PricingModel={'dCPC'}>} cost)*Margin,Campaign_ID))
I realised that in there are 2 steps needed for this calculation:
1. Sum(Cost) AS SUM, CampaignId (only for PricingModel = 'dCPC')
3. Sum(SUM)
Explanation:
becuase i need to multiply the Sum(Cost) by Margin (only for PricingModel = 'dCPC'), and because i know that a Margin is defined by the Campain, I need to first to "prepare" a data set that is grouped by CampignID (see the AGGR part of the expression above).
So, first i prepare a set that Sum the cost per Campaign (and therefore the Sum can be multiply by the Margin, which depends on the Campign).
Then, i need to Sum these results to get the revenue for all the campaigns.
And that Works,
Thanks!
Out of interest, Guy, what do you do when there is no margin linked to the campaign as I think the result will be null?
Hi Jason,
The expression: =Sum(AGGR(Sum( {<PricingModel={'dCPC'}>} cost)*Margin,Campaign_ID))
inheretely deals with Margin that is Null, because the AGGR returns 0 this way, and therefore when a campaign has no Margin defined, the calulation for him will be 0, and not Null.
I attached the fixed QVW.
Have a good day!
Guy
Got it. Cheers.