Sum of values where ID = max ID for a group based on selection

[Blunck-TotalFitouts]

Hi team, 

I have a table that has the following columns.  This table is connected to a mastercalendar via the dateinvoiceissued column.

Simpro jobid	JobTotalExTax	dateinvoiceissued	invoiceid	totalextax_fixed	WorkInProgress
155.AU	$15,511.95	4/01/2016	342	$15,511.95	$0
156.AU	$2,676.84	14/12/2015	317	$2,676.84	$0
157.AU	$502.36	4/01/2016	343	$502.36	$0
163.AU	$6,976.08	1/12/2015	284	$1,395.22	$5,580.86
169.AU	$129,999.98	10/12/2015	314	$38,999.98	$91,000
169.AU	$129,999.98	11/01/2016	357	$26,000	$65,000
169.AU	$129,999.98	11/01/2016	358	$26,000	$39,000
32.AU	$23,977.29	24/08/2015	51	$19,855	$4,122.29
32.AU	$23,977.29	24/09/2015	123	$4,122.29	$0
38.AU	$18,824.07	11/08/2015	42	$5,647.22	$13,176.85
38.AU	$18,824.07	15/09/2015	108	$13,176.86	-$0.01
40.AU	$37,934.26	7/09/2015	84	$7,586.85	$30,347.41
40.AU	$37,934.26	4/11/2015	201	$30,347.41	$0
41.AU	$39,000	14/08/2015	90	$7,800	$31,200
41.AU	$39,000	7/09/2015	81	$15,600	$15,600
41.AU	$39,000	17/09/2015	116	$15,600	$0
44.AU	$7,215	16/09/2015	111	$7,215	$0
49.AU	$6,359.95	4/11/2015	204	$6,359.95	$0
62.AU	$38,007.93	7/09/2015	83	$7,601.59	$30,406.34
62.AU	$38,007.93	24/09/2015	124	$30,406.34	$0
65.AU	$54,219.07	7/09/2015	82	$10,208.32	$44,010.75
65.AU	$54,219.07	24/09/2015	121	$40,833.29	$3,177.46
65.AU	$54,219.07	14/10/2015	153	$3,177.46	$0
68.AU	$909.09	24/09/2015	122	$909.09	$0
72.AU	$1,595.36	15/09/2015	109	$1,595.36	$0
75.AU	$1,638	16/09/2015	110	$1,638	$0
76.AU	$1,021.20	16/09/2015	112	$1,021.20	$0
77.AU	$210	16/09/2015	113	$210	$0
80.AU	$2,154.78	24/09/2015	125	$2,154.78	$0
81.AU	$50,104.30	16/09/2015	115	$15,031.29	$35,073.01
81.AU	$50,104.30	4/11/2015	206	$20,041.72	$15,031.29
81.AU	$50,104.30	4/12/2015	298	$15,031.29	$0
92.AU	$5,133.13	4/11/2015	205	$5,133.13	$0
97.AU	$931.52	4/11/2015	203	$931.52	$0
99.AU	$612	4/11/2015	202	$612	$0

I need to be able calculate the total WorkInProgress amount but only based on the values where the invoiceid = the max (invoice) for each Simpro jobid.  

The result should change each time a date from the dateinvoiceissued is selected.

So for example, without any date selected, the total WorkInProgress would be $44,508.85.  

But if a date of 3/01/2016 was selected, the total WorkInProgress would be $96,580.85.

Tried this formula from a solution to a similar post in the qlikview community, but it doesn't work in qliksense, or I have updated it incorrectly...

=Sum({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}, dateinvoiceissued>}Aggr(FirstSortedValue({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}, invoiceid>}WorkInProgress, -invoiceid), [Simpro jobid]))

Assistance on this is greatly appreciated!!

Cheers

Carl

[Aasir]

Modify your expression


=Sum({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}, Simpro={'*'}>} TOTAL <Simpro jobid> {<invoiceid = {"=$(=Max({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}>} invoiceid))"}>} WorkInProgress)

[Blunck-TotalFitouts]

Hey @Aasir ,

Thanks mate.  That expression has an error in it, fixed it as best I could but still getting errors.

Error message on your version = '>' expected

My version = =Sum({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}, [Simpro jobid]{'*'}>} TOTAL <Simpro jobid> {<invoiceid = {"<=$(=Max({<dateinvoiceissued "} = {"<=$(=Max(dateinvoiceissued))"}>} invoiceid))"}>} WorkInProgress)

With error message = Error in set modifier expression.

Attached is some sample data if you want to load it up and test it.  Appreciate the help!

Thanks
Carl

[Aasir · Accepted solution]

=Sum({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}>} TOTAL
Aggr(
FirstSortedValue({<dateinvoiceissued = {"<=$(=Max(dateinvoiceissued))"}>} WorkInProgress, -invoiceid),
[Simpro jobid]
)
)

[blunky]

Legend!