Problems summing a calculated field

[#1]

Hello,

Please find attached an excerpt of a straight table I made on Qlikview.

The final two columns are dimensions. Impressions are available in the loaded data while revenue is an expression I made myself.

The formula for it is simply (Impressions/1000) * CPM Rate.

I have 2 bar charts

They both run off the same drill down group of Publication>Advertiser Name>Order Name>Drop ID as their one dimension

The first bar chart has an expression of sum(Impressions) and works fine. In this excerpt it is able to show a bar for campaign 1 that tells me there were 1,925,143 impressions

The second has an expressions sum((Impressions/1000) * CPM Rate) and is wildly over calculating the revenue, which should be around £18k.

So my question is what is the correct expression when summing a 'calculated field'.

NB

The Drop ID is a sub part of a Campaign and has its own CPM, So I feel the answer may revolve around this - although as you can see a single Drop can appear on more than one of my publications.

[sunny_talwar]

Would you be able to share your application to look at it?

[#3]

HI Sunny

N‌ot really. It contains lots of sensitive information hence the anonymised excerpt.

What i'be given is just 1 campaign among thousands so it's also very large.

What I'm after is anyone with experience summing a multiplication. The sum of impressions works fine but as soon as I sum impressions multiplied by a dimension it falls apart!

[pho3nix90]

Hi Russell

Without seeing an example application there isn't much that can be done. Since your example csv you provided are the "perfect" example of how not to receive such an error.

However, I will suggest to have a look at your joins and make sure they are unique as possible. What you are seeing will most likely be duplication of data on joins which is causing the calculation to sum more than you are actually expecting.

If you need further help, remove actual sensitive data from the application like company/client name address etc, also look into Settings->Document->Scrambling to scramble sensitive data to upload.

[sunny_talwar]

I would not expect the sum to not be right, but this is based on the sample you have provided. May be if I can guide you towards scrambling your data, you might be able to share you application? Try this link:

Preparing examples for Upload - Reduction and Data Scrambling

[#6]

Hi everyone,

I have made some progress. I'm sorry I haven't uploaded a scrambled anonymised qvd but will do next time.

As I've mentioned it is a big dataset and in a straight table I'm using the following expression to get revenue...

if([Adbook Price Type]='CPM UK',([Adbook Price]*[DFP Impressions]/1000),if([Adbook Price Type]='Flat Rate UK',[Adbook Price],([Line Item Rate]*[DFP Impressions]/1000)))

Yes the dimension names are different in real life (I have 2 CPM rates and a Flat Rate!)

This seems to work fine in a table and interestingly avoids all mention of summing - bizarrely I've tried to recreate the table and this expression returns nothing! But I have one working table at least.

What was basically going wrong when I was making my charts was just a placement of brackets.

For the first chart sum(Impressions) is absolutely fine and can be cut and sliced by any dimension.

When it comes to revenue it turn out you can't write, for example, sum(([Line Item Rate]*[DFP Impressions]/1000)) - it gives massively enlarged answers.

you can write

sum([DFP Impressions]/1000)*[Line Item Rate]

i.e. leave the summing to the impressions only

Furthermore to really get a chart that makes sense the aggr function needs to be thrown in

sum(aggr(sum([DFP Impressions]/1000)*[Adbook Price],[Order Name],[Drop ID]))

This appears to define the relationship between Drop ID and Orders nicely

I'm sure I'll be back on here soon - I'm dreading the necessary formula for Flat Rate campaigns considering how complicated this simple scenario for one CPM rate has been.

Cheers

Russell