Straight Table - past year of sales

[#1]

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

[sunny_talwar]

The best way would be to create an As-of table in the script: The As-Of Table

[#3]

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.

[sunny_talwar]

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.

[#5]

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

[sunny_talwar]

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)))

[#7]

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.

[sunny_talwar]

Would you be able to share few rows of data to see how it looks?

[#9]

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?