Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OAS Date in DV manual query - Parameter / Filter
Answers
-
I have not given up, but the proposed solution is not adequate, so I am still looking for an alternative solution
0 -
What exactly is not adequate? You have to think about your use case first and how it makes sense with regards to your data.
From your original post:
where open_date <= '31.12.2020'
and nvl(close_date, '31.12.2020') >= '31.12.2020'
If this is your definition of a "Year" as you want to have it as a let's say "canonical time" then the definition is potenitally very wrong. I don't know your data in detail, but any row where the open_date and close_date bridge two years will never get picked up. for example.
Open: 12th August 2023
Close: 4th May 2024
What "Year" is that with regards to what you want as your canonical time dropdown/filter?
Second Example:
Open: 27th December 2022
Close: 17th January 2024
What year is that? Where would that row be picked up by the filter?
My suggestions above weren't more precise because the base ask that you have isn't clear.
Only you can tell us what the real requirement is. Technical representation of the requirement is just an implementation detail.
0 -
Thanks for your effort.
I will try to explain, I don't know if we didn't understand the problem itself, or if I don't understand the answer well because English is not my native language.
If I take the example you gave with open: August 12, 2023 and close: May 4, 2024. I want to know if the account was active on December 31, 2023, so in this case it was. But, in theory, there is no date 31 Dec 2023 in the open and close_date dataset, so I can't select it from those columns. I also didn't understand your concept of "year", because the date is important to me (instead of December 31, 2023, I might be interested in e.g. February 2, 2024).
I hope that I wrote down clearly and thank you once again for your effort!
0 -
Hi,
I'm jumping in here just having read "pieces" of the thread (but hopefully I got the important pieces).
In DV, in your workbook (as Christian said, the date filters aren't in your dataset, that one should be without filters to allow the user to select filters at the workbook level), you can create a parameter and define it as a date.
Doing that, when you use that parameter as filter, in the filter bar of the workbook or in a dashboard filters visualization, the values will be selected freely in a calendar.
You can then reference the parameter in your filter for your visualization (or filter at the workbook level). This parameter will not be linked to any column or existing data, it's just a variable with a date value.
By the way, in your query in the question you use dates as text, I hope your database has real date columns for open_date and close_date and not text, otherwise you will need to convert those to dates in the dataset because your parameter will be a date, not a text.
0 -
How I create a date parameter? I tried adding a column to the table, but it was a dead end. Please guide me
0 -
0 -
I tried adding a column to the table, but it was a dead end.
As said above, you should forget the dataset (I assume that what you called table is your dataset). Your query gives you a dataset. In that one, you should remove, delete fully, your WHERE conditions. Your dataset will be without any filter.
You then define parameters, and filters, in a workbook (which is built using a dataset as source of data). OAS will take the filters selected in the workbook and automatically add them to the query without filters of your dataset.
Then, when you fixed your dataset and moved into a workbook, look at to learn all you need to know to get started with parameters (it is for OAC, therefore the pictures could be slightly different if referencing something released on OAC only, but 99% should be matching OAS 2024).
0 -
@Gianni Carrero thanks for pointing me to the instructions, they were very helpful
@Christian Berg -Oracle yes, I followed the first recommendation and removed the where clause. Now I create parameter, the only thing bothering me is that I have to choose the value below, so here I chose 31.12.2023
when creating the filter, I also encounter an error - what should I set as the default value, given that I want a free choice?
if I delete the last bracket (as I thought from your picture) I still get the error
0 -
just as an example (don't have the time to build it all in real time) here's a parameter filled from a data set using LSQL
0
