topic Re: formula difference columns same header in QlikView

formula difference columns same header

Tue, 20 Oct 2015 15:03:34 GMT

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?

Re: formula difference columns same header

sinanozdemir — Tue, 20 Oct 2015 16:51:51 GMT

Hi,

Here is the expression that I used:

Hope this helps.

Re: formula difference columns same header

Thu, 22 Oct 2015 12:43:21 GMT

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.

Re: formula difference columns same header

sinanozdemir — Thu, 22 Oct 2015 12:53:47 GMT

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?

Re: formula difference columns same header

Thu, 22 Oct 2015 12:55:59 GMT

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

Re: formula difference columns same header

sinanozdemir — Fri, 23 Oct 2015 15:40:25 GMT

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.

Re: formula difference columns same header

sinanozdemir — Fri, 23 Oct 2015 20:53:05 GMT

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

Re: formula difference columns same header

Mon, 26 Oct 2015 18:51:43 GMT

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

Re: formula difference columns same header

sinanozdemir — Mon, 26 Oct 2015 19:10:28 GMT

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.