Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this table :
PLANT | WORKSHOP | CIRCUIT | ACTIVITY | PERIOD | VALUE |
SITE1 | WK1 | CIRCUIT1 | Electricity | 01/01/2019 | 2000 |
SITE1 | WK1 | CIRCUIT1 | Gas | 01/01/2019 | 500 |
SITE1 | WK1 | CIRCUIT1 | Hot Water | 01/01/2019 | 300 |
SITE1 | WK1 | CIRCUIT1 | Production_Month | 01/01/2019 | 72000 |
SITE1 | WK1 | CIRCUIT1 | Electricity | 01/02/2019 | 1015 |
SITE1 | WK1 | CIRCUIT1 | Gas | 01/02/2019 | 100 |
SITE1 | WK1 | CIRCUIT1 | Hot Water | 01/02/2019 | 200 |
SITE1 | WK1 | CIRCUIT1 | Production_Month | 01/02/2019 | 50000 |
SITE1 | WK1 | CIRCUIT1 | Electricity | 01/03/2019 | 950 |
SITE1 | WK1 | CIRCUIT1 | Gas | 01/03/2019 | 0 |
SITE1 | WK1 | CIRCUIT1 | Hot Water | 01/03/2019 | 0 |
SITE1 | WK1 | CIRCUIT1 | Production_Month | 01/03/2019 | 60000 |
SITE1 | WK1 | CIRCUIT1 | Electricity | 01/04/2019 | 1071 |
SITE1 | WK1 | CIRCUIT1 | Gas | 01/04/2019 | 50 |
SITE1 | WK1 | CIRCUIT1 | Hot Water | 01/04/2019 | 100 |
SITE1 | WK1 | CIRCUIT1 | Production_Month | 01/04/2019 | 80000 |
SITE1 | WK1 | CIRCUIT1 | Electricity | 01/05/2019 | 1241 |
SITE1 | WK1 | CIRCUIT1 | Gas | 01/05/2019 | 40 |
SITE1 | WK1 | CIRCUIT1 | Hot Water | 01/05/2019 | 0 |
SITE1 | WK1 | CIRCUIT1 | Production_Month | 01/05/2019 | 20000 |
SITE2 | WK3 | CIRCUIT10 | Electricity | 01/01/2019 | 2000 |
SITE2 | WK3 | CIRCUIT10 | Gas | 01/01/2019 | 500 |
SITE2 | WK3 | CIRCUIT10 | Hot Water | 01/01/2019 | 300 |
SITE2 | WK3 | CIRCUIT10 | Production_Month | 01/01/2019 | 72000 |
SITE2 | WK3 | CIRCUIT10 | Electricity | 01/02/2019 | 1015 |
SITE2 | WK3 | CIRCUIT10 | Gas | 01/02/2019 | 100 |
SITE2 | WK3 | CIRCUIT10 | Hot Water | 01/02/2019 | 200 |
SITE2 | WK3 | CIRCUIT10 | Production_Month | 01/02/2019 | 50000 |
SITE2 | WK3 | CIRCUIT10 | Electricity | 01/03/2019 | 950 |
SITE2 | WK3 | CIRCUIT10 | Gas | 01/03/2019 | 0 |
SITE2 | WK3 | CIRCUIT10 | Hot Water | 01/03/2019 | 0 |
SITE2 | WK3 | CIRCUIT10 | Production_Month | 01/03/2019 | 60000 |
SITE2 | WK3 | CIRCUIT10 | Electricity | 01/04/2019 | 1071 |
SITE2 | WK3 | CIRCUIT10 | Gas | 01/04/2019 | 50 |
SITE2 | WK3 | CIRCUIT10 | Hot Water | 01/04/2019 | 100 |
SITE2 | WK3 | CIRCUIT10 | Production_Month | 01/04/2019 | 80000 |
SITE2 | WK3 | CIRCUIT10 | Electricity | 01/05/2019 | 1241 |
SITE2 | WK3 | CIRCUIT10 | Gas | 01/05/2019 | 40 |
SITE2 | WK3 | CIRCUIT10 | Hot Water | 01/05/2019 | 0 |
SITE2 | WK3 | CIRCUIT10 | Production_Month | 01/05/2019 | 20000 |
I used a cross table to transform to this :
PERIOD | 1/1/2019 | 2/1/2019 | 3/1/2019 | 4/1/2019 | 5/1/2019 | ||||||||
PLANT | WORKSHOP | CIRCUIT | TMS | Data | Data | Data | Data | Data | |||||
SITE1 | WK1 | CIRCUIT1 | Electricity | 2000 | 1015 | 950 | 1071 | 1241 | |||||
SITE1 | WK1 | CIRCUIT1 | Gas | 500 | 100 | 0 | 50 | 40 | |||||
SITE1 | WK1 | CIRCUIT1 | Hot Water | 300 | 200 | 0 | 100 | 0 | |||||
SITE1 | WK1 | CIRCUIT1 | Production_Month | 72000 | 50000 | 60000 | 80000 | 20000 | |||||
SITE2 | WK3 | CIRCUIT10 | Electricity | 2000 | 1015 | 950 | 1071 | 1241 | |||||
SITE2 | WK3 | CIRCUIT10 | Gas | 500 | 100 | 0 | 50 | 40 | |||||
SITE2 | WK3 | CIRCUIT10 | Hot Water | 300 | 200 | 0 | 100 | 0 | |||||
SITE2 | WK3 | CIRCUIT10 | Production_Month | 72000 | 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 :
PERIOD | 1/1/2019 | 2/1/2019 | 3/1/2019 | 4/1/2019 | 5/1/2019 | ||||||||||||
PLANT | WORKSHOP | CIRCUIT | TMS | Data | Ratio | Data | Ratio | Data | Ratio | Data | Ratio | Data | Ratio | ||||
SITE1 | WK1 | CIRCUIT1 | Electricity | 2000 | 0,028 | 1015 | 0,020 | 950 | 0,016 | 1071 | 0,013 | 1241 | 0,062 | ||||
SITE1 | WK1 | CIRCUIT1 | Gas | 500 | 0,007 | 100 | 0,002 | 0 | 0,000 | 50 | 0,001 | 40 | 0,002 | ||||
SITE1 | WK1 | CIRCUIT1 | Hot Water | 300 | 0,004 | 200 | 0,004 | 0 | 0,000 | 100 | 0,001 | 0 | 0,000 | ||||
SITE1 | WK1 | CIRCUIT1 | Production_Month | 72000 | 1,000 | 50000 | 1,000 | 60000 | 1,000 | 80000 | 1,000 | 20000 | 1,000 | ||||
SITE2 | WK3 | CIRCUIT10 | Electricity | 2000 | 0,028 | 1015 | 0,020 | 950 | 0,016 | 1071 | 0,013 | 1241 | 0,062 | ||||
SITE2 | WK3 | CIRCUIT10 | Gas | 500 | 0,007 | 100 | 0,002 | 0 | 0,000 | 50 | 0,001 | 40 | 0,002 | ||||
SITE2 | WK3 | CIRCUIT10 | Hot Water | 300 | 0,004 | 200 | 0,004 | 0 | 0,000 | 100 | 0,001 | 0 | 0,000 | ||||
SITE2 | WK3 | CIRCUIT10 | Production_Month | 72000 | 1,000 | 50000 | 1,000 | 60000 | 1,000 | 80000 | 1,000 | 20000 | 1,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
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))
Anyone to help me ?
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))
Thank you very much ! It's good !!