Complicated calculation in a chart

[#1]

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

[Jason_Michaelides]

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

[swuehl · Accepted solution]

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

[#4]

Are the result correct if you change the expression to:

= Sum( {<Traff_Campaigns.PricingModel={'dCPC'}>} DISTINCT cost*Traff_Campaigns.Margin)

?

[swuehl]

And your margins are probably given in Percent, so you need to divide by 100.

[#6]

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!

[Jason_Michaelides]

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?

[#8]

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

[Jason_Michaelides]

Got it.  Cheers.