I have a table where currently the date dimension [Fiscal Week End Date] is selected.
However, for a given selected date, I would need to display for a record the sum of [Sellin Quantity] between [Fiscal Week End Date] - 5 days to the selected [Fiscal Week End Date].
But I am not able to achieve that and I am always getting the sum only for the selected Fiscal Week End Date.
What am I doing wrong?
Any help is appreciated.
Eg: The filter selected would be [Fiscal Week End Date] = 24-May-2024.
The total sum for 21st May 2024 to 24th May 2024 is 37800. But the sum I get is only for 24th May 2024 which is 36000
| Fiscal Week End Date | Sellin Qty |
|---|---|
| 24-May-2024 | 30000 |
| 24-May-2024 | 6000 |
| 22-May-2024 | 1800 |
The script I have used is as follows:
alt
(
sum({<[Fiscal Week End Date]={">=$(=((Max({<[Fiscal Week End Date] = >}[Fiscal Week End Date])) -'5'))<=$(=((Max({<[Fiscal Week End Date] = >}[Fiscal Week End Date]))))"}>} [Sellin Quantity])
)
Try this formula:
RangeSum(Above(Aggr(Sum([Selling Qty]), ([Fiscal Week End Date], (Numeric, Descending))), 0, 5))
That gives the desired result for me.
But it will react to filtering on [Fiscal Week End Date].
I couldn’t get your set expression to work but I think the problem is that you think Max([Fiscal Week End Date]) will refer to the current row in your table, which is not the case. Set Expressions are evaluated before the table dimension is taken into account.
Test data I have produced:
Data:
LOAD Date, Date(Date, 'DD-MMM-YYYY') as [Fiscal Week End Date], [Selling Qty]
Inline [
Date, Selling Qty
01.01.2021, 1000
01.01.2021, 2000
02.01.2021, 1500
03.01.2021, 2300
04.01.2021, 5000
05.01.2021, 1200
06.01.2021, 3000
07.01.2021, 1000
08.01.2021, 3400
09.01.2021, 2200
];