How to insert row and calculate value based on above two rows

[Learner]

Hello,

I want to calculate insert two rows and calculate EBITDA Rs/T & EBITDA Rs Cr. based on NSR & Cos. My data is as follows:

Unit	Group	CHD	GGBS
DLV	Production_Sales qty.	10000	5000
DLV	NSR Part	5000	2000
DLV	Cos	4000	900
NDL	Production_Sales qty.	11000	6000
NDL	NSR Part	5100	2100
NDL	Cos	4200	950

I have made App like this:

Unit	Group	CHD	GGBS
DLV	Production_Sales qty.	10000	5000
	NSR Part	5000	2000
	Cos	4000	900
NDL	Production_Sales qty.	11000	6000
	NSR Part	5100	2100
	Cos	4200	950

What i want is the yellow lines as shown below:

EBITDA Rs/T is Row no. 2 less row no. 3 in Unit DLV same for NDL unit.

EBITDA Rs Cr. is Row no. 4 * Row no. 1 in Unit DLV same for NDL unit.

Please help me out in the above report......

with regards,

Ashish

[QFabian · Accepted solution]

Hi @Learner , please see if this is an acceptable solution for you :

The final number for 'EBITDA Rs Cr.' is the unique difference, please check

DataX:
LOAD * INLINE [
Unit, Group, CHD, GGBS
DLV, Production_Sales qty., 10000, 5000
DLV, NSR Part, 5000, 2000
DLV, Cos, 4000, 900
NDL, Production_Sales qty., 11000, 6000
NDL, NSR Part, 5100, 2100
NDL, Cos, 4200, 950
];

Data:
Load
AutoNumber(Group, Unit) as Id,
Unit,
Group,
CHD,
GGBS
Resident DataX;
drop table DataX;
concatenate
Load distinct
Unit,
4 as Id,
'EBITDA RS / T' as Group
Resident Data;
concatenate
Load distinct
Unit,
5 as Id,
'EBITDA Rs Cr.' as Group
Resident Data;

Data2:
Load
1 as Id2,
Unit,
Group,
Id,
if(Id = 4, previous(previous(CHD)) - previous(CHD),
if(Id = 5, peek(CHD2) / previous(previous(previous(previous(CHD)))), CHD)) as CHD2,

if(Id = 4, previous(previous(GGBS)) - previous(GGBS),
if(Id = 5, peek(GGBS2) / previous(previous(previous(previous(GGBS)))), GGBS)) as GGBS2

Resident Data
order by
Unit,
Id;
drop table Data;

[Learner]

Many Thanks...... it does work......

But  is there any way to do this in front end i.e. in Expression and Dimension. we can add extra row and write formula as row no. 3 minus row no. 2. I am asking this because if there are lots of Columns & row than it will be difficult for write formula for each column(as in the example there are only 2 columns CHD & GGBS).

Thanking you,

Ashish

[marcus_sommer]

You don't need to calculate all values within the script but you need to add all artificial/extra dimension-values for all totals respectively intermediate calculations which aren't feasible with the partial sums within the object.

This means in your case something like:

concatenate(YourTable) load pick('EBITDA1', 'EBITDA2', recno()) as Group autogenerate 2;

adding just the dimension-values to your field. If they are there you could query them within your chart expressions like:

if(Group = 'EBITDA1', expr1, Group = 'EBITDA2', expr2, expr3))

If there are more as two such special calculations it becomes useful if there are flag-values which indicates if certain values are positive or negative to avoid further if-loops querying and branching for this information.

A step further in this logic would be to integrate the extra dimension-values within an The As-Of Table - Qlik Community - 1466130 which would avoid any branching and a simple sum(value) would be enough - unless by any rate-calculations which I wouldn't mix with the sums else applying it within an extra column.

- Marcus

[Learner]

Thank you for your response.......

I tried typing the concatenate function in script but ,as i am new to scripting,  it did not worked may be i have written incorrectly. Here is what i wrote:

DataX:
LOAD * INLINE [
Unit, Group, CHD, GGBS
DLV, Production_Sales qty., 10000, 5000
DLV, NSR Part, 5000, 2000
DLV, Cos, 4000, 900
NDL, Production_Sales qty., 11000, 6000
NDL, NSR Part, 5100, 2100
NDL, Cos, 4200, 950
];

concatenate(DataX)
load
pick('EBITDA1', 'EBITDA2', recno()) as Group autogenerate 2;

but no extra row was created.

Screen shot of Chart & list box both.(EBITDA1 & EBITDA2 does not appears)

Please let me know where i am making mistake.

Thanking you,

Ashish

[marcus_sommer · Accepted solution]

Sorry, I mistake the parameter-order of pick() - therefore change it to:

concatenate(DataX)
load
pick(recno(), 'EBITDA1', 'EBITDA2') as Group autogenerate 2;

- Marcus

[Learner]

Thank you it worked...............