Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
awp2020
Contributor III
Contributor III

calculate ratio with cross table

Hello, 

I have this table :

PLANTWORKSHOPCIRCUITACTIVITYPERIODVALUE
SITE1WK1CIRCUIT1Electricity 01/01/20192000
SITE1WK1CIRCUIT1Gas 01/01/2019500
SITE1WK1CIRCUIT1Hot Water 01/01/2019300
SITE1WK1CIRCUIT1Production_Month 01/01/201972000
SITE1WK1CIRCUIT1Electricity 01/02/20191015
SITE1WK1CIRCUIT1Gas 01/02/2019100
SITE1WK1CIRCUIT1Hot Water 01/02/2019200
SITE1WK1CIRCUIT1Production_Month 01/02/201950000
SITE1WK1CIRCUIT1Electricity 01/03/2019950
SITE1WK1CIRCUIT1Gas 01/03/20190
SITE1WK1CIRCUIT1Hot Water 01/03/20190
SITE1WK1CIRCUIT1Production_Month 01/03/201960000
SITE1WK1CIRCUIT1Electricity 01/04/20191071
SITE1WK1CIRCUIT1Gas 01/04/201950
SITE1WK1CIRCUIT1Hot Water 01/04/2019100
SITE1WK1CIRCUIT1Production_Month 01/04/201980000
SITE1WK1CIRCUIT1Electricity 01/05/20191241
SITE1WK1CIRCUIT1Gas 01/05/201940
SITE1WK1CIRCUIT1Hot Water 01/05/20190
SITE1WK1CIRCUIT1Production_Month 01/05/201920000
SITE2WK3CIRCUIT10Electricity 01/01/20192000
SITE2WK3CIRCUIT10Gas 01/01/2019500
SITE2WK3CIRCUIT10Hot Water 01/01/2019300
SITE2WK3CIRCUIT10Production_Month 01/01/201972000
SITE2WK3CIRCUIT10Electricity 01/02/20191015
SITE2WK3CIRCUIT10Gas 01/02/2019100
SITE2WK3CIRCUIT10Hot Water 01/02/2019200
SITE2WK3CIRCUIT10Production_Month 01/02/201950000
SITE2WK3CIRCUIT10Electricity 01/03/2019950
SITE2WK3CIRCUIT10Gas 01/03/20190
SITE2WK3CIRCUIT10Hot Water 01/03/20190
SITE2WK3CIRCUIT10Production_Month 01/03/201960000
SITE2WK3CIRCUIT10Electricity 01/04/20191071
SITE2WK3CIRCUIT10Gas 01/04/201950
SITE2WK3CIRCUIT10Hot Water 01/04/2019100
SITE2WK3CIRCUIT10Production_Month 01/04/201980000
SITE2WK3CIRCUIT10Electricity 01/05/20191241
SITE2WK3CIRCUIT10Gas 01/05/201940
SITE2WK3CIRCUIT10Hot Water 01/05/20190
SITE2WK3CIRCUIT10Production_Month 01/05/201920000

 

I used a cross table to transform to this :

   PERIOD1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 
PLANTWORKSHOPCIRCUITTMSData Data Data Data Data 
SITE1WK1CIRCUIT1Electricity 2000 1015 950 1071 1241 
SITE1WK1CIRCUIT1Gas 500 100 0 50 40 
SITE1WK1CIRCUIT1Hot Water 300 200 0 100 0 
SITE1WK1CIRCUIT1Production_Month72000 50000 60000 80000 20000 
SITE2WK3CIRCUIT10Electricity 2000 1015 950 1071 1241 
SITE2WK3CIRCUIT10Gas 500 100 0 50 40 
SITE2WK3CIRCUIT10Hot Water 300 200 0 100 0 
SITE2WK3CIRCUIT10Production_Month72000 50000 60000 80000 20000 

 

But I would like to add a ratio that divides the ACTIVITY = (electricity, steam, gas, hot water) field by production month.

like this :

   PERIOD1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019     
PLANTWORKSHOPCIRCUITTMSDataRatioDataRatioDataRatioDataRatioDataRatio    
SITE1WK1CIRCUIT1Electricity 20000,02810150,0209500,01610710,01312410,062    
SITE1WK1CIRCUIT1Gas 5000,0071000,00200,000500,001400,002    
SITE1WK1CIRCUIT1Hot Water 3000,0042000,00400,0001000,00100,000    
SITE1WK1CIRCUIT1Production_Month720001,000500001,000600001,000800001,000200001,000    
SITE2WK3CIRCUIT10Electricity 20000,02810150,0209500,01610710,01312410,062    
SITE2WK3CIRCUIT10Gas 5000,0071000,00200,000500,001400,002    
SITE2WK3CIRCUIT10Hot Water 3000,0042000,00400,0001000,00100,000    
SITE2WK3CIRCUIT10Production_Month720001,000500001,000600001,000800001,000200001,000    

 

I used this for ratio :

=if(Dimensionality()=4, Sum({<PERIOD = {">=1/1/2019<=$(=MonthEnd(Max(PERIOD)))"},ACTIVITIE = {'Electricity','Gas','Steam','Production_Month','Hot Water'}>}VALUE)/
Sum({<PERIOD = {">=1/1/2019<=$(=MonthEnd(Max(PERIOD)))"},ACTIVITIE = {'Production_Month'}>} VALUE))

But it does not work ,

Can you help me please ?

 

Thx

 

 

1 Solution

Accepted Solutions
stevejoyce
Specialist II
Specialist II

Not sure if you intentionally misspelled the Activity field in your expression (activitie).  But your issue is in your denominator, see below with update:

=if(Dimensionality()=4, Sum({<PERIOD = {">=1/1/2019<=$(=MonthEnd(Max(PERIOD)))"},ACTIVITY = {'Electricity','Gas','Steam','Production_Month','Hot Water'}>}VALUE)/
Sum(Total<PLANT, WORKSHOP, PERIOD> {<PERIOD = {">=1/1/2019<=$(=MonthEnd(Max(PERIOD)))"},ACTIVITY = {'Production_Month'}>} VALUE))

View solution in original post

3 Replies
awp2020
Contributor III
Contributor III
Author

Anyone to help me ?

stevejoyce
Specialist II
Specialist II

Not sure if you intentionally misspelled the Activity field in your expression (activitie).  But your issue is in your denominator, see below with update:

=if(Dimensionality()=4, Sum({<PERIOD = {">=1/1/2019<=$(=MonthEnd(Max(PERIOD)))"},ACTIVITY = {'Electricity','Gas','Steam','Production_Month','Hot Water'}>}VALUE)/
Sum(Total<PLANT, WORKSHOP, PERIOD> {<PERIOD = {">=1/1/2019<=$(=MonthEnd(Max(PERIOD)))"},ACTIVITY = {'Production_Month'}>} VALUE))

awp2020
Contributor III
Contributor III
Author

Thank you very much ! It's good !!