1 of 1 people found this helpful
Create a parameter with a list of values corresponding to your periods.
Then create a calculated field like;
IF parameter = "today" and [Sale Date] = then TRUE else FALSE
ELSEIF parameter = "1w" and [Sale Date] >= dateadd('day',-7,today()) and [Sale Date] <= today() then TRUE else FALSE
Then drag the calculated field onto the filter shelf, set it to true and enable the parameter control.
Tom give you a standard way to bring in varying ranges of dates.
I'm curious about your statement that you want a conditional format based on the range selected.
Where will you be displaying the date value? If it will be in a column then you can calc a format conditionally by converting the date to string and formatting it as a string value in the calc based on the parameter selection. But if you want to display it on an axis, ... you might need to use sheet swapping to display it one way or another.
And what date are you actually going to display? If the user selects "1y" for example, are you still displaying one date value? Or 365 date values?
Take a look at the error description, it says maybe you are missing an identifier.
On your first line you have nothing after the = sign. It should be and [Open Date] = TODAY()
Tom -- The example you initially gave was missing TODAY(). I noticed that and was going to comment, but I figured Henrique would quickly figure it out.
Henrique -- You have to understand that when people provide suggested code for a calc, we're often typing it off the top of our heads. You have to help yourself too.
What Tom suggested is the right way to go. Getting correct syntax has to be up to you.
We should be able to swap the label without having to swap sheets here. It's going to take some playing around though. Can you upload a sample workbook so that I can play with it?
Thanks for help Joe,
I've tried to use Today in that case before but it does't work.
I've attached the file.You will see that it' possible to change the labels by parameter, but i can't aggregate by 3months for example.
You need to add this to the top of the date range formula
if [Period] = "day"
if [Order Date] = [DateSelect] then 1 else 0 end
elseif...... other cases
I think I'm beginning to get my head wrapped around your requirements. See attached. (Note to self: V10.2 here.)
You already have the logic for [Label] right.
And initially I had created a calc to pretend that TODAY() is actually 2016, but then I saw what you did with [DateSelect], so I went with that instead.
Let's look at [DateRange]. I made some changes in there. I added a "day" chunk. Basic stuff.
I modified "week" to grab [DateSelect] plus 6 additional days back.
I made the biggest change with "month". I used DATETRUNC to truncate both the [Order Date] and the [DateSelect]. This will grab everything in the selected month up to the selected day. Your logic was grabbing back 30 days, so it was going into the prior month too. (Although, maybe you actually wanted that.)
I changed "quarter" to be actual quarterly date manipulation. However, that actually grabs data into the future -- whatever belongs in this calendar quarter.
I created a whole new category called "3M" (and "3 month" internally in the parameter.) I think this is what you are really looking for. Get this month up to the selected date, and show the prior two months as well.
And I added a 1Y section to the DateRange calc.
Wow, Thank you Joe Oppelt!!!
It was exactly what i'm looking for.
I tried a lot of things before but can't find the right idea.
You find out a very smart solution to this problem.
Thanks a lot!!!