topic Re: Return Value from Field based on Week Number in App Development

Return Value from Field based on Week Number

simoncarts — Tue, 12 Jul 2022 13:52:01 GMT

Good afternoon All,

Does anyone know how to return a sum of values from a Dimension based on the current week number matching the Dimension Name?

When the below table is loaded into Qlik, columns are loaded as dimensions as usual.

What is the expression required to return the SUM of values from lets say Week 16?

I'd have a Week(Today()) expression (to return the Current Week Number) included so that the currents week target is always displayed.

Shop	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52
Meat Shop	2	4	6	8	10	12	14	16	18	20	22	24	26	28	30	32	34	36	38	40	42	44	46	48	50	52	54	56	58	60	62	64	66	68	70	72	74	76	78	80	82	84	86	88	90	92	94	96	98	100	102	104
Fish Shop	4	8	12	16	20	24	28	32	36	40	44	48	52	56	60	64	68	72	76	80	84	88	92	96	100	104	108	112	116	120	124	128	132	136	140	144	148	152	156	160	164	168	172	176	180	184	188	192	196	200	204	208
Vegetable Shop	5	10	15	20	25	30	35	40	45	50	55	60	65	70	75	80	85	90	95	100	105	110	115	120	125	130	135	140	145	150	155	160	165	170	175	180	185	190	195	200	205	210	215	220	225	230	235	240	245	250	255	260
Fruit Shop	3	6	9	12	15	18	21	24	27	30	33	36	39	42	45	48	51	54	57	60	63	66	69	72	75	78	81	84	87	90	93	96	99	102	105	108	111	114	117	120	123	126	129	132	135	138	141	144	147	150	153	156

Re: Return Value from Field based on Week Number

MayilVahanan — Tue, 12 Jul 2022 14:01:00 GMT

Hi
Try like below

CrossTable(WeekNum, Values)
Load * from ursource;

Exp: Sum({<WeekNum={$(=Week(Today()))}>}Values)

Re: Return Value from Field based on Week Number

simoncarts — Tue, 12 Jul 2022 14:13:41 GMT

Thanks for the quick response!

Where would I insert that in this code?

Set dataManagerTables = '','Tabelle1';
//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;

Unqualify *;

[Tabelle1]:
LOAD
[Shop],
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31],
[32],
[33],
[34],
[35],
[36],
[37],
[38],
[39],
[40],
[41],
[42],
[43],
[44],
[45],
[46],
[47],
[48],
[49],
[50],
[51],
[52]
FROM [lib://Targets.xlsx]
(ooxml, embedded labels, table is Tabelle1);