Calculation based on another expression answer

[Learner]

Hello everyone,

I am new to Qlikview. I have developed an app. with some complex formulas with my data base and created groups.

It looks like this:

Unit	Group	FG/SFG	CHD		Unit of Measurement
DLV	Production_Sales qty.		11327		MT
DLV	NSR Part		3491		Rs/T
DLV	Cos		2826		Rs/T
DLV	EBITDA Rs/T		665		Rs/T
DLV	EBITDA Rs Cr.		0.75		Rs Cr.
NDL	Production_Sales qty.		124073		MT
NDL	NSR Part		2946		Rs/T
NDL	Cos		2116		Rs/T
NDL	EBITDA Rs/T		830		Rs/T
NDL	EBITDA Rs Cr.		10.30		Rs Cr.
SLB	Production_Sales qty.		9366		MT
SLB	NSR Part		3503		Rs/T
SLB	Cos		3918		Rs/T
SLB	EBITDA Rs/T		-415		Rs/T
SLB	EBITDA Rs Cr.		-0.39		Rs Cr.
VJN	Production_Sales qty.		0.00		MT
VJN	NSR Part		0.00		Rs/T
VJN	Cos		0.00		Rs/T
VJN	EBITDA Rs/T		0.00		Rs/T
VJN	EBITDA Rs Cr.		0.00		Rs Cr.
JCL	Production_Sales qty.		144766.00		MT
JCL	NSR Part		3024.68		Rs/T
JCL	Cos		2288.14		Rs/T
JCL	EBITDA Rs/T		736.54		Rs/T
JCL	EBITDA Rs Cr.		10.66		Rs Cr.

In above unit of measure column is just for reference, which i have created in excel for reference.

What i want to do is the part which i have marked in Bold & Italic letters. I have calculated figures for CHD (Column Heading) product for all the four Units i.e. DLV, NDL,SLB & VJN. My data base also has the figures with these units. Now i wanted consolidated figures for all the four plant with unit name JCL(marked in bold). The calculation must be based on the figures already calculated for the four different units(the figures are above the bold part). I mean is there a way to calculate the figures based on the figures which are there unit wise. for e.g. JCL - Production_Sales qty. must be 144766 which is sum of all the four unit's(DLV,NDL,SLB,VJN) for Group Production_sales qty.(11327+124073+9366+0) in the same way for NSR parts it must do sumproduct for wieghted average NSR for JCL.

My 1st task is to create extra 5 lines with JCL in Units column & the five common fields in Group column. 2nd is to calculate the figures based on the figures of units(DLV,NDL,SLB,VJN) as mentioned above.

Will be grateful for the help............

Ashish 

[anthonyj]

Hi,

You can use group by and concatenate to do your calculations and union them to the bottom of the table. You mentioned that there's different aggregations based on the group. I don't know what the weights are for your averages but if you use this pattern with your data you'll be able to union the aggregations to the bottom of your table.

Load your table first, then add this underneath.

Concatenate
Load
'JCL' as Unit,
Group,
sum(CHD) as CHD,
[Unit of Measurement]
Resident Data
where Group = 'Production_Sales qty.'
group by Group, [Unit of Measurement] ;

Add your next aggregation with relevant "where" condition under this and they will union to your table. 

I hope this helps.

Regards

Anthony

[Learner]

Thank you for your reply......

I am not good enough in script writing, so please give solution which you gave above by ignoring Unit of Measurement column.....

My data is loaded as follow(in edit script mode):

Grouping:
LOAD * INLINE [
Group, Category
Production_Sales qty., Production_Sales qty.
NSR Part, NSR Part
Cos, Raw Material
Cos, Power
Cos, Material Handling
Cos, Consumable
Cos, Salary-Prod.
Cos, Outsource
Cos, Rep. & Maint.
Cos, Increase/Decrease
Cos, Salary-Marketing
Cos, Salary-Corporate
Cos, Marketing OH
Cos, Corporate OH
Cos, Advertisement
EBITDA Rs/T, BIS
EBITDA Rs/T, Fuel
EBITDA Rs Cr., Overheads

];

Main_Data:
LOAD Month,
Category,
[FG/SFG 1],
[FG/SFG],
Product,
Unit,
Production,
[Self-Cons.],
Sale,
Qty.,
Rate,
YearName(Month,0,4) as Financial_Year,
Amt.




FROM
[D:\DRIVE G\Ashish JSW\office data old\Cost and Profitability Report\Data Bank\Data for Qlikview upload.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thanks and regards,

Ashish

[anthonyj]

Hi Ashish,

We all started somewhere. Qlik has a very powerful scripting language to transform your data and in your case these extra rows can be created by concatenating the calculated rows to the bottom. So your 1st and 2nd tasks are done at the same time.

The script provided below performs the sum aggregation over the two groups. (I assumed that EBITDA Rs Cr. was summed as well so these can go in the same group). You mentioned that NSR Part is to be aggregated by a weighted average. I'm not sure what your parameters are for this so I've just provided the code for averaging this field.

There's a thread here on how to perform sumproduct and weighted average in Qlik.

https://community.qlik.com/t5/New-to-Qlik-Sense/Weighted-Averages-Excel-SUMPRODUCT/td-p/810069

Concatenate
Load
'JCL' as Unit,
Group,
sum(CHD) as CHD
Resident Data
where match(Group, 'Production_Sales qty.', 'EBITDA Rs Cr.')
group by Group;

Concatenate
Load
'JCL' as Unit,
Group,
avg(CHD) as CHD
Resident Data
where Group = 'NSR Part'
group by Group;

Regards

Anthony

[Learner]

I tried the above formula it give error as Field 'Group' not found............

is it because i have created Group through inline load statement????

If it is because of inline load statement., than how i have to refer Group in your Script??

Thanks and regards,

Ashish

[anthonyj]

Hi Ashish,

Judging by the code you provided previously it may be that Group and CHD may be on different tables so these would need to be joined before performing any calculations and summarization. It might help if you posted what your model looks like so we can see if they're in different tables.

Regards

Anthony