Hi,
Can anyone please let me know what's wrong with the below expression?
I'm trying to create 2 functions in the text box as below:
| Reporting Date | Total RRP |
|---|
| 2016/04/03 | $91,907,319 |
| 2016/03/27 | $93,333,504 |
| 2016/03/20 | $94,149,907 |
| Reporting Date | Category A | Category B | Category C |
|---|
| 2016/04/03 | $15,234,570 | $50,000,000 | $26,672,749 |
| 2016/03/27 | $14,754,582 | $50,000,000 | $28,578,922 |
| 2016/03/20 | $14,345,164 | $50,000,000 | $29,804,743 |
Text Box 1: I want text box 1 to return the value of Category A when a reporting date is selected, however when the reporting date is not specified, I want it to return the value of Category A of the latest reporting date.
=NUM(SUM({$<[Reporting Date]={"$(=max([Reporting Date]))"}>}{<Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0', '.',',')
Text Box 2: I want text box 2 to return the value of Category A of the prior reporting week, however when the reporting date is not specified, it should still return the value of Category A of the prior week of the latest report.
=NUM(SUM({$<[Reporting Date]={"$(=date(WeekStart(max([Reporting Date]),-1,-1),'YYYY/MM/DD'))"}>} {$<Category={'Surplus'}>} [Total RRP]),'$ #,##0', '.',',')
Basically when Reporting Date - "2016/03/27" is selected, I want Text Box 1 to return $14,754,582 and Text Box 2 to return $14,345,164
When reporting date is NOT selected, Text Box 1 should return $15,234,570 and Text Box 2 should return $14,754,582.
Currently, for some reason box of my text boxes are returning the same value, when the reporting date is not specified, both text box 1 & 2 return the total value of Category A - $54,334,316 of ALL reports, when for example reporting date "2016/03/27" is selected, both text box 1&2 are returning $14,754,582.
Thank you
Susan
