Enquiry on difference between rows

[#1]

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.

[pokassov]

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)

[#3]

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

[pokassov]

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

[#5]

Any idea how to do this in the load script?

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

[vikramv]

Hi,

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

Thanks,

Vikram.

[anat]

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)

[anat]

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

[pokassov · Accepted solution]

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;

[#10]

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;