Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
I think this is an easy one........
I have a table that I want to show the past 365 days of sales based on the date showing in the dimension.
Date (Dimension) | Current Stock (Expr) | Sales(Expr) | Past 365 Sales (Expr) |
---|---|---|---|
01/01/2016 | 100 | 1 | 106 |
02/01/2016 | 102 | 2 | 108 |
03/01/2016 | 105 | 3 | 109 |
04/01/2016 | 110 | 5 | 112 |
I think it is something with set analysis - Date>=Date-365 ?
Thanks
The best way would be to create an As-of table in the script: The As-Of Table
No way of doing it in the expression? My Script is a bit complicated and if possible I'd like to avoid making things more complex.
Yes you can, you can use a combination of RangeSum(Above(YourExpression, 0, 365)). Not sure if this would work as it is, but you can try to play around with this a little bit.
presumably this would need me to have the table showing all dates? I want it to work if I filter to one date showing the sales for that day and then the previous 365 days of sales. If this isn't possible then not to worry
You can still filter and it would still work smoothly with selections. I would do something like this (Assuming your expression is this -> Sum(Sales))
If(Sum(Sales) > 0, RangeSum(Above(Sum({<Year, Date, Month, MonthYear, Quarter, QuarterYear>}Sales), 0, 365)))
The only problem I see is that unless you have a dynamic way to going back 366 days in leap year such as 2016, your expression will always go back 365 days. If you can, I would create a field in the script to determine how many days back your date needs to go back. This would be easy to script:
Date - AddYears(Date, -1) as Days
and then may be this:
If(Sum(Sales) > 0, RangeSum(Above(Sum({<Year, Date, Month, MonthYear, Quarter, QuarterYear>}Sales), 0, Days)))
unfortunately my data set isn't as nice as having a line for each day - it has product information along with other things so I'm not sure this is going to work....
Apologies I haven't been clear enough. I thought I would be able to write set analysis for sales where date is greater than the dimension date less 365.
Would you be able to share few rows of data to see how it looks?
The issue is I'm pulling from a couple of data sources so my Date runs across all information but I have some null values for my "sales" field. Also my date field is a time stamp.
Date | Item | Purchases | Sales |
---|---|---|---|
01/01/2016 00:00 | A | - | 1 |
02/01/2016 01:13 | B | 5 | - |
03/04/2016 02:18 | C | 1 | - |
05/04/2016 03:22 | D | - | 8 |
Does this make sense?