# App Development

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Contributor III

## Calculation based on another expression answer

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

5 Replies
Creator III

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.

Concatenate
'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

Contributor III
Author

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:
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
EBITDA Rs/T, BIS
EBITDA Rs/T, Fuel

];

Main_Data:
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

Creator III

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
'JCL' as Unit,
Group,
sum(CHD) as CHD
Resident Data
where match(Group, 'Production_Sales qty.', 'EBITDA Rs Cr.')
group by Group;

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

Regards

Anthony

Contributor III
Author

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

Creator III

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

Tags
Community Browser