How to retrieve a value based on a month selected by the user in Analytics?
I have been investigating about how to retrieve a value based on a month selected by the user in analytics, for instance, let's say that the user has selected the January, internally now I am converting this to its last day 01/31/2017 (January = 01/31/2017, February = 02/28/2017 and so on), I want to get this day to be used in the query showed below and then to use the value retrieved ( CONVERSION_RATE) to be multiply by the amount showed in the image.
My problem is that I do not know where and how to store or set a variable with the value of the last day, and I do not know how to get the value retrieved by the query.
WHERE TRUNC(GDR.CONVERSION_DATE) =to_date(V_DATE, 'YYYY-MM-DD')
AND GDR.FROM_CURRENCY ='CRC'
AND GDR.TO_CURRENCY ='USD'
AND GDR.CONVERSION_TYPE = '1001'
Before someone says that OBIEE is not to write queries (This happend to me once) I already know this, but I think I need to do this now, because my time dimension does not include the dates that I required, so I need to do somehow a direct query to the database.
I does not matter how is the solution, I just want to find the solution as you think could be.
Please, Do not confused log user information with the selection made by the user, as it was confused in another forum, Please just participate members with a real possible solution, with real added-value.