Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In addition you might need to add Date() to your set analysis:
=Num(Sum({$<[Reporting Date]={"$(=Date(Max([Reporting Date]), 'YYYY/MM/DD'))"}, Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0')
If this still doesn't work, would you be able to provide a sample where you have Category?
Dear Susan,
=NUM(SUM({$<[Reporting Date]={"$(=max([Reporting Date]))"}, Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0')
Kind regards,
Ishfaque Ahmed
In addition you might need to add Date() to your set analysis:
=Num(Sum({$<[Reporting Date]={"$(=Date(Max([Reporting Date]), 'YYYY/MM/DD'))"}, Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0')
If this still doesn't work, would you be able to provide a sample where you have Category?