1 of 1 people found this helpful
I'm not sure that I completely understand the third category--24 months ago from today to 36 months from today.
However, for the first two categories, two calculated fields can be created similar to the following:
Today Minus 24 Months (this will show data prior to 24 months ago):
if [Order Date]<=dateadd('month', -24, today()) then 'show' else 'hide' end
Last 24 Months:
if [Order Date]<=today() and [Order Date]>=dateadd('month', -24, today()) then 'show' else 'hide' end
Then, create a string parameter that lists out these categories, and create another calculated field similar to the following:
case [Date Range Parameter]
when 'Previous to 24 Months' then [Today Minus 24 Months]
when 'Last 24 Months' then [Last 24 Months]
Place this on the view. Right click on an instance of hide, and select Hide. Then, right click on the parameter and select Show Parameter Control.
Hope this helps a bit!
So I created my set of ranges using a formula similar to ones you outlined, but I have repeat data in my lists now. So I have sales history data and each row is a separate transaction and I want to show a list of say just the customers who most recent purchase was in the past 24 months. and another list of customers where their most recent purchase was between 24-36 months. and then a final list of customers who have not made a purchase in the past 24 months. ( I realize there will be will be overlap with list 2 and 3.
so far i set up a formula to break down the date ranges but I dont know how to show the customer list without having duplicates for each transaction they made. Right now in the last 24 month range, it is listing a customer 5 times if they made 5 purchases during that range, I only need them placed by their most recent purchase.
Sale within past 24 months :
IF DATEADD('month',-24,NOW()) <=[OrderDate] AND [OrderDate]<=NOW() THEN 'True' ELSE 'False' END
Sale within 24-36:
IF DATEADD('month',-24,NOW()) > [OrderDate] and[OrderDate]> DATEADD('month',-36,NOW()) THEN 'True' ELSE ' False' END
Sale not within 24 months:
IF DATEADD('month',-24,NOW()) >[OrderDate] THEN 'True' ELSE 'False' END
If I understand correctly, you only want to see a list of the customer's last purchase date within the selected range. By creating another calculated field similar to the following should help you achieve this:
if max([Order Date])=window_max(max([Order Date])) then 'true' else 'false' end
Place your Customer then Order Date on the rows shelf. Then, place the above calculation to the right of these. Right click on the calculation and select Edit Table Calculation... Under Compute Using, select Advanced. Place your Customer field and then the Order Date field to the Compute Using box. Click OK. Choose Deepest for At the level and Customer for Restarting every. Click OK. Then, right click on the calculation again and select Filter and check only true.
From here, you can remove the calculation if desired, and Order Date and Customer can be moved around.
I have attached a sample workbook.
Hope this helps!