Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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)
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
The easiest way I think is add Expenses into Field and calculate correct values during script.
Any idea how to do this in the load script?
Since you can't have set analysis to be done in load
Hi,
I guess you need to make it as a generic load and then manipulate in the expressions.
Thanks,
Vikram.
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)
How to Create a Profit and Loss Statement in QlikView follow above link for reference .
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;
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;