topic Complicated calculation in a chart in QlikView

Complicated calculation in a chart

Tue, 28 Feb 2012 17:07:43 GMT

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

Complicated calculation in a chart

Jason_Michaelides — Tue, 28 Feb 2012 17:16:17 GMT

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

Complicated calculation in a chart

swuehl — Tue, 28 Feb 2012 17:22:14 GMT

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

Complicated calculation in a chart

Tue, 28 Feb 2012 17:22:19 GMT

Are the result correct if you change the expression to:

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

Re: Complicated calculation in a chart

swuehl — Tue, 28 Feb 2012 17:39:24 GMT

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

Complicated calculation in a chart

Wed, 29 Feb 2012 09:39:23 GMT

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!

Complicated calculation in a chart

Jason_Michaelides — Wed, 29 Feb 2012 09:44:15 GMT

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?

Re: Complicated calculation in a chart

Wed, 29 Feb 2012 12:31:14 GMT

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

Re: Complicated calculation in a chart

Jason_Michaelides — Wed, 29 Feb 2012 12:42:17 GMT

Got it. Cheers.