Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts!
I've a simple table with ID, OrderDate and Sales.
I also have another table which is not linked with the above table. This table has a list of dates like a calendar.
I want to be able to select any date from Calendar and then show all the OrderDate which >= Calendar Date and <= CalendarDate+15days.
For example if I select date 01/04/2016 I get this list:
Now what I actually want is a straight table that looks like this:
Is this something possible?
I'm attached the app and the data if anyone can have a look!
Hi,
what is your dimension??
use calendar date as your dimension.
Expression
sum(if(orderDate<=CalendarDate and orderDate>=CalendarDate+15,Sales)
Regards,
Prashant
Hi,
Try this as your calculated dimension:
=aggr(sum(distinct{<OrderDate={">=$(=max(Calendar))<=$(=max(Calendar)+15)"}>} OrderDate),OrderDate)
Do this after ensuring that both OrderDate and Calendar are in the number format during data-load. Manage the date-display format in the display objects.
May be you meant this (incorrect signs )
Sum(If(OrderDate >= Calendar and OrderDate <= (Calendar+15), Sales))
Like this?
thanks sunny
Yes this might work. I'll do further testing on this but is there anyway I can see the repeated Code and sales value as soon as they become available in the chart. like this?
ideally i want to have the calculated dimension rather than building this logic in expression.
Hi Karthik
I want this logic to be build in calculated dimension not at the expression level.