Add ratio in crosstable - Qlik Community

awp2020 · ‎2021-09-23

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