Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Learner
Contributor III
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
anthonyj
Creator III
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.

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
Contributor III
Contributor III
Author

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
Creator III
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
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
Contributor III
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

 

 

anthonyj
Creator III
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