Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Straight Table - past year of sales

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/20161001106
02/01/20161022108
03/01/20161053109
04/01/20161105112

I think it is something with set analysis - Date>=Date-365 ?

Thanks

8 Replies

Re: Straight Table - past year of sales

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

Not applicable

Re: Straight Table - past year of sales

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.

Re: Straight Table - past year of sales

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.

Not applicable

Re: Straight Table - past year of sales

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

Re: Straight Table - past year of sales

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

Not applicable

Re: Straight Table - past year of sales

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.

Re: Straight Table - past year of sales

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

Not applicable

Re: Straight Table - past year of sales

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.

DateItemPurchasesSales
01/01/2016 00:00A-1
02/01/2016 01:13B5-
03/04/2016 02:18C1-
05/04/2016 03:22D-8

Does this make sense?