topic Enquiry on difference between rows in QlikView

Enquiry on difference between rows

Thu, 30 Apr 2015 06:46:07 GMT

Dear fellow experts,

I have a table like this which is loaded in the script

Field Location YTD Month Budget

Turnover Japan 100 20 110

Gross margin Japan 40 10 50

Operating profit Japan 10 2 20

Turnover India 1000 500 1100

Gross margin India 600 200 750

Operating profit India 300 100 350

I will like to show operating expense as difference between Operating profit and gross margin in a straight table.

Is this possible? Bearing in mind that my table is pretty huge with a few hundred rows as there are more than 2 locations as in the example above.

Re: Enquiry on difference between rows

pokassov — Thu, 30 Apr 2015 10:05:37 GMT

Hello!

Dimensions:

Location

Expressions:

sum({$<Field={'Operating profit'}>} Budget)-sum({$<Field={'Gross margin'}>} Budget)

sum({$<Field={'Operating profit'}>} YTD)-sum({$<Field={'Gross margin'}>} YTD)

sum({$<Field={'Operating profit'}>} Month)-sum({$<Field={'Gross margin'}>} Month)

Re: Enquiry on difference between rows

Thu, 30 Apr 2015 10:10:16 GMT

Hi,

I dont think it'll give the correct answer what i like is something like this at the end

Field Location YTD Month Budget

Turnover Japan 100 20 110

Gross margin Japan 40 10 50

Expenses Japan -30 -8 -30

Operating profit Japan 10 2 20

Turnover India 1000 500 1100

Gross margin India 600 200 750

Expenses India -300 -100 -400

Operating profit India 300 100 350

Re: Enquiry on difference between rows

pokassov — Thu, 30 Apr 2015 10:14:23 GMT

The easiest way I think is add Expenses into Field and calculate correct values during script.

Re: Enquiry on difference between rows

Thu, 30 Apr 2015 10:17:15 GMT

Any idea how to do this in the load script?

Since you can't have set analysis to be done in load

Re: Enquiry on difference between rows

vikramv — Thu, 30 Apr 2015 10:17:42 GMT

Hi,

I guess you need to make it as a generic load and then manipulate in the expressions.

Thanks,

Vikram.

Re: Enquiry on difference between rows

anat — Thu, 30 Apr 2015 10:24:54 GMT

create one excel like below:

Dims,Ind

Turnover ,1

Gross margin ,1

Operating profit,1

Turnover ,1

Gross margin ,1

Operating profit,1

operating expense,0

create one variable like:

VOperatingExpenses=sum({< Dims='{Operating profit}'amt>})-sum({< Dims='{gross margin}'amt>})

take Dims as a dimension and in table Expression level write like this:

if(Ind=0 then $(VOperatingExpenses) else amt)

Re: Enquiry on difference between rows

anat — Thu, 30 Apr 2015 10:27:01 GMT

How to Create a Profit and Loss Statement in QlikView follow above link for reference .

Re: Enquiry on difference between rows

pokassov — Thu, 30 Apr 2015 10:28:32 GMT

t1:

noconcatenate

load

Location,

YTD,

Month,

Budget

resident yourtable

where Field='Operating profit';

left join (t1)

load

Location,

YTD as YTD2,

Month as Month2,

Budget as Budget2

resident yourtable

where Field='Gross margin';

t2:

noconcatenate load

'Expenses' as Field,

Location

YTD-YTD2 as YTD,

Month-Month2 as Month,

Budget-Budget2 as Budget

resident t1;

drop table t1;

concatenate (yourtable)

load * resident t2;

drop table t2;

Re: Enquiry on difference between rows

Mon, 04 May 2015 05:31:48 GMT

Hi Сергей Покасов,

Many thanks for the script above..

It worked fine for me..

However, i have one question. Why when i initially put it as like this:(see bold text) , nothing came out?

t1:

noconcatenate

load

Field

Location,

YTD,

Month,

Budget

resident yourtable

where Field='Operating profit';

left join (t1)

load

Field

Location,

YTD as YTD2,

Month as Month2,

Budget as Budget2

resident yourtable

where Field='Gross margin';

t2:

noconcatenate load

'Expenses' as Field,

Location

YTD-YTD2 as YTD,

Month-Month2 as Month,

Budget-Budget2 as Budget

resident t1;

drop table t1;

concatenate (yourtable)

load * resident t2;

drop table t2;

Re: Enquiry on difference between rows

pokassov — Tue, 05 May 2015 09:09:25 GMT

Hello!

When you add field "Field" it's used for join.

In the same time values in fields (t1 and "left join to t1") are different.

So you have joined null values for Field, Loaction, ...Budget2

When after that you try to calculate different between YTD and YTD2 (which is null), you would take null...

Sergey