Pivot tbl. calculated field - sum of two "regular" fields but only if Field1 matches certain value?

[astrbac]

Hi all,

I have a pretty simple problem - I wish to create a calculated field in my pivot table. This calculated field needs to SUM two fields, that already exist in the data source, but with a little twist. Here is an example which will make this much more clear.

Source data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Market[/TD]
[TD]Platform[/TD]
[TD]Web sales $[/TD]
[TD]Mobile app sales $[/TD]
[/TR]
[TR]
[TD]FR[/TD]
[TD]ios[/TD]
[TD]1323[/TD]
[TD]8709[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD]ios[/TD]
[TD]12434[/TD]
[TD]7657[/TD]
[/TR]
[TR]
[TD]FR[/TD]
[TD]android[/TD]
[TD]234[/TD]
[TD]2352355[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD]android[/TD]
[TD]12323[/TD]
[TD]23434[/TD]
[/TR]
</tbody>[/TABLE]



The result pivot table that I need to create:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Market[/TD]
[TD]Total sales $[/TD]
[/TR]
[TR]
[TD]FR[/TD]
[TD](Web sales + Mobile sales)[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD](Web sales + Mobile sales)[/TD]
[/TR]
</tbody>[/TABLE]



The problem:

In the source data, field:Mobile app sales $ needs to be divided by 2 IF field:Platform equals "iOS". (We have a data collection problem that cannot be corrected for now so I have to make do). Yes, I know that one option is to simply intervene in the source data sheet but I wish to avoid that as much as possible. Pseudo-formula would look like this:



This is what I tried but it's not working:

='Website conversion value' +IF(Platform=OR("iPhone"; "iPod"; "iPad");'Mobile app purchases conversion value' /2;'Mobile app purchases conversion value' )

If I solve this on the pivot table side, then I can simply refresh my source data with new .csv imports which I append to the end of existing data.

Many thanks!
Alex

 

[Norie]

Alex

Unfortunately you can't use an IF statement in a calculated field for text values.

 

[astrbac]

Dang ... so I WILL have to create a helper column in source data, have it "listen" for incoming dataset and when it finds iPhone, iPad, iPod write 1, for all else it should write 0. Then use that helper column in calculated field formula, right?

Thanks for your time Norie!