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
