Set Analysis max date

marwen_garwachi — Wed, 25 Nov 2020 16:16:04 GMT

Hi community,

I have a pivot table which displays by task the quantity presented in DTD over a selected period and I would like to display the quantity of Stock recorded at the end of the last working day.

i'm using this set analysis : SUM({$< %Date = {'=Max(%Date)'}>} [Qté Stock] )

but it's not working, do someone know why ?

For exemple :

my data looks like this :

Task ID	Task Desk	%Date	DTD	DMT	DTD Needed ETP	Stock	Stock Needed ETP
T01	Task 1	05/03/2018	150	00:03:00	1,0	300	2,0
T02	Task 2	05/03/2018	200	00:01:00	0,4	400	0,9
T03	Task 3	05/03/2018	100	00:05:00	1,1	200	2,2
T04	Task 4	05/03/2018	150	00:03:00	1,0	300	2,0
T05	Task 5	05/03/2018	238	00:01:00	0,5	476	1,1
T06	Task 6	05/03/2018	254	00:01:30	0,8	508	1,7
T07	Task 7	05/03/2018	1000	00:03:00	6,7	2000	13,3
T08	Task 8	05/03/2018	25	00:01:30	0,1	50	0,2
T09	Task 9	05/03/2018	3256	00:01:30	10,9	6512	21,7
T10	Task 10	05/03/2018	100	00:01:30	0,3	200	0,7
T01	Task 1	06/03/2018	50	00:03:00	0,3	100	0,7
T02	Task 2	06/03/2018	30	00:01:00	0,1	60	0,1
T03	Task 3	06/03/2018	150	00:05:00	1,7	300	3,3
T04	Task 4	06/03/2018	200	00:03:00	1,3	400	2,7
T05	Task 5	06/03/2018	100	00:01:00	0,2	200	0,4
T06	Task 6	06/03/2018	23	00:01:30	0,1	46	0,2
T07	Task 7	06/03/2018	50	00:03:00	0,3	100	0,7
T08	Task 8	06/03/2018	500	00:01:30	1,7	1000	3,3
T09	Task 9	06/03/2018	200	00:01:30	0,7	400	1,3
T10	Task 10	06/03/2018	50	00:01:30	0,2	100	0,3

> I need to show only the Last Worked Day Stock Qty and the sum of DTD so the results should looks like :

Task Desk	DTD	DMT	DTD Needed ETP	Stock	Stock Needed ETP
Task 1	200	00:03:00	1,3	100	0,7
Task 2	230	00:01:00	0,5	60	0,1
Task 3	250	00:05:00	2,8	300	3,3
Task 4	350	00:03:00	2,3	400	2,7
Task 5	338	00:01:00	0,8	200	0,4
Task 6	277	00:01:30	0,9	46	0,2
Task 7	1050	00:03:00	7,0	100	0,7
Task 8	525	00:01:30	1,8	1000	3,3
Task 9	3456	00:01:30	11,5	400	1,3
Task 10	150	00:01:30	0,5	100	0,3

Thanks for your help

Marwen

Re: Set Analysis max date

sunny_talwar — Mon, 12 Mar 2018 17:30:52 GMT

Try this

Sum({$<%Date = {"$(=Max(%Date))"}>} [Qté Stock])

or this

Sum({$<%Date = {"$(=Date(Max(%Date), 'MM/DD/YYYY'))"}>} [Qté Stock])

Re: Set Analysis max date

sasiparupudi1 — Tue, 13 Mar 2018 07:47:52 GMT

Try

sum({<%Date={'$(=Maxstring(%Date))'}>}Stock)

sum({<%Date={'$(=Max(%Date))'}>}Stock)

Re: Set Analysis max date

shiveshsingh — Tue, 13 Mar 2018 07:56:35 GMT

Try this

SUM({$< %Date = {"$(=Max(%Date))"}>} [Qté Stock] )

Re: Set Analysis max date

marwen_garwachi — Tue, 13 Mar 2018 08:05:35 GMT

Thanks Sunny !

it worked with Sum({$<%Date = {"$(=Max(%Date))"}>} [Qté Stock])

topic Re: Set Analysis max date in QlikView

Set Analysis max date

Re: Set Analysis max date

Re: Set Analysis max date

Re: Set Analysis max date

Re: Set Analysis max date