formula difference columns same header

[#1]

Hello I have a pivot table

Dimensions Year and Seller

Rows Month, Day

Cells "solds",  "different solds"

e.g

          Year 2015                         2014

          Seller Andrew Eveline       Andrew Eveline

oct. 19           100       300            50         200

oct. 18           200        400           60         100

Now i want to add a column difference, but the total amount should be the difference between the same seller

e.g.

          Year 2015                                                                      2014

          Seller Andrew  difference     Eveline     difference            Andrew Eveline

oct. 19           100           50                300           100                     50         200

oct. 18           200          140                400          300                     60         100

does anyone know the formula?

[sinanozdemir]

Hi,

Here is the expression that I used:

Hope this helps.

[#3]

Hello, That would be correct if you always have the same order in columns.

But when you have a year e.g. 2014 with the name Carol, which is not in 2015 seller, than the formula doesn't function well.

[sinanozdemir]

In that case, you can nest your function within an if statement. If there is no record then do something else something else.

Can you provide a better data-set that reflects your issue?

[#5]

Hello Sinan,

You are doing very well!

Now i want to add a column difference, but the total amount should be the difference between the same seller

e.g.

          Year 2015                                                                      2014

          Seller Andrew  difference     Eveline     difference            Andrew Carol Eveline

oct. 19           100           50                300           100                     50        80   200

oct. 18           200          140                400          300                     60        90   100

[sinanozdemir]

Hi,

Since Caroline doesn't exist in 2015, it might be difficult to use Before() or After() functions in this case because they refer to the columns. I am thinking of a possible solution:

You sort your data by Seller, Month and Year and incorporate and If statement to get the difference at a row level and in your pivot table, you can just sum Data and Difference columns:

This solution assumes that your data is already aggregated.

Hope this helps.

Also attaching the qvw.

[sinanozdemir]

Hi,

And this is solution 2 by using After(), but I had to populate data for missing seller, Caroline which could be optimized:

This is the inline table that holds the data:

As you can see Caroline doesn't have any data for 2015. In the below script, I first populated the missing data for 2015 and placed 0 since Caroline didn't have any data in 2015:

Here is the data model after running the above statement:

And finally, in the pivot table I used After() function:

I am attaching the qvw as well.

Thanks

[#8]

Very Very Good, but i don't understand the script.

[sinanozdemir]

You may need to break it piece by piece to see what each script does. However, in a nutshell here is what each piece does:

Temp1:

LOAD

  Seller,

  If(RowNo() = 1, Flag, If(Flag <> Previous(Flag), Previous(Flag), Flag)) As Key1; -----> Now we are comparing which seller doesn't have                                                                                                                                the same year and month. The first record always                                                                                                                                gets flagged as it has all the desired years and                                                                                                                                months. The rest will copy the year and month                                                                                                                                data that the first record has.

LOAD

  Seller,

  Concat(Distinct Year & '-' & Month, '|') As Flag ------> Concatenates year and month, and then groups by seller                                                                                                                       names so that we have per record per seller. This will help                                                                                                                    us to determine what year and month are missing per seller.

Resident Sample

Group By Seller;

NoConcatenate

Temp2:

LOAD

  Seller & '|' & Key2 As Key; ------> With preceding load, we are creating a key to join the main table. The key prevents syn tables and                                                          circular references.

LOAD

  Seller,

  SubField(Key1, '|') As Key2 ------> Now, we are splitting the concatenated records by seller so we are building the missing records.

Resident Temp1;

Join ------> Joining to the main table to see which records have null values.

LOAD

  *,

  Seller & '|' & Year & '-' & Month As Key ------> Creating the same key in the preceding load.

Resident Sample;

Main:

LOAD

  If(IsNull(Seller), TextBetween(Key, '', '|'), Seller) As Seller, -----> Determining whether the field is null.

  If(IsNull(Year), TextBetween(Key, '|', '-'), Year) As Year, -----> Determining whether the field is null.

  If(IsNull(Month), TextBetween(Key, '-', ''), Month) As Month, -----> Determining whether the field is null.

  If(IsNull(Data), 0, Data) As Data, -----> Determining whether the field is null.

  'New Table' As Indicator -----> Just an indicator of the new fields.

Resident Temp2;

Drop Tables Sample, Temp1, Temp2; -----> Dropping temp tables.

Drop Field Indicator From Main; -----> Dropping the indicator field you don't have to, but I chose to do.

I think the best is way to go through each piece and definition of functions in order to understand what each piece does.

Hope this helps.