Hi,
We need to fetch some accounts that have not made any purchases in the last 3 months. In SSMS I use below code, but this seems to be not working correctly when I use it in the cube (as table query).
SUM(Amount) OVER (order by Date ROWS BETWEEN 3 PRECEDING AND current row) = 0
Would appreciate any inputs on how to use this in the cube. Thank you!
I've had sparklines not show data if there were null values included in my data. Could that be the case here?
EDIT: replied to wrong post. Apologies.
Iris
Assuming you have dimACCOUNTS and factPURCHASE tables, I would create a custom column (ageFILTER) in the factPURCHASE table to use as a filter for accounts that have not made a purchase in last 3 months. That ageFILTER column code would look something like:
case
when [order by Date] <= addmonths(now(),-3) then 'Old' ---- Old is a row with a date older than 3 months
else 'New' ---- New is a row with a purchase in the last 3 months
end
NOTE: using now() in the addMonths function assumes your data is updated through today. If you build once a day and your ElastiCube data is updated through yesterday you can adjust for this by using addmonths(adddays(now(),-1),-3). This subtracts a day from now.
On my dashboard I would create a pivot table with a row column listing ACCOUNTS. I would then create a widget filter based on my new ageFILTER column. The ageFILTER column will have values OLD and NEW. Unselect the NEW value. That should leave customers who have only OLD values. They will not have made a purchase in the last 3 months.
Hope this helps, Jim
