11 Replies Latest reply on Sep 9, 2015 4:59 PM by 2885278

    Filter for 12 months ago

    2885278

      Hello guys

       

      I have an issue for displaying data for 12 months ago using dashboard prompt.

       

      First I have created dashboard prompt namely:

       

      --> Choose Month: December 2014

       

      the issue I faced was how can I make month range  for example If I chose December 2014 then the formula will give 12 months ago.actually I have tried using this

      "Time"."Date" <= cast(current_date as date) and "Time"."Date" >= TIMESTAMPADD(SQL_TSI_MONTH, -12, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

       

      my question: Is it posibble to change CURRENT_DATE with presentation variable? from my sample above I really want to change CURRENT_DATE with December 2014 as my Presentation Variable. as for information that my presentation variable is Varchar as Data Type.

       

      Please guys,if you have any suggestions, advices and solutons, I really appreciates a lot.

        • 1. Re: Filter for 12 months ago
          Joel Acha

          You'll need to setup your column in Dashboard Prompt so that it populates a Presentation Variable.

           

          dashboard prompt.JPG

          You can then reference the Presentation Variable in your column formula to calculate 12 months ago from the date of the Presentation Variable.

          • 2. Re: Filter for 12 months ago
            Gianni Ceresa

            Hi,

            It's a bit more than what Joel Acha posted as simply with presentation variable you will have an error as it isn't a date (and you know it as you wrote it).

            So your challenge is not about the filter itself but just how to get a date out from a varchar presentation variable.

             

            There are many ways to do it, you can of course do it by some CASE WHEN parsing the varchar and matching January = 1, February = 2 etc. and the year.

            If your higher granularity is the month level (you don't have days in your model) I hope you also have a real date (first day of the month or last of the month is the most common) in your time dimension at the same level of your month column. In that case you add a filter on your varchar month column but use the real date column in your filter: the prompted value filter the varchar value which will fix the value of the real date column for your month.

             

            Another option can be convert your text into a date directly by using TO_DATETIME.

            This function acts a bit like TO_DATE in Oracle DB, the syntax is:

            TO_DATETIME('string1', 'DateTime_formatting_string')

             

            So you can try to match the format string to read your own varchar date (you can have issues with languages if you have multiple languages and things like that but you can always give it a try ...).

            • 3. Re: Filter for 12 months ago
              Joel Acha

              Hi Gianni Ceresa, yes, you're right, the PResentation Variable will come across as a string but this can be CAST to a DATE in the column formula and dealt with accordingly.

              • 4. Re: Filter for 12 months ago
                Gianni Ceresa

                I don't want to be pessimistic but ... CAST of that kind of varchar to date using "CAST" will give you something like this:

                Capture.PNG

                (the formula is CAST('December 2014' as DATE) on SampleApp406)

                CAST isn't good for these things, and I'm also scared that "December 2014" can't be matched by To_DateTime neither.

                It's probably the worst possible format for a date ("Dec 2014" or "2014.12" work well with To_DateTime for example).

                And I'm almost sure that in your time dimension you have a column working find with To_DateTime (you must have something like 2014-12 or 2014.12 or just "month number", and with "year" you can concatenate, to sort your months or April 2014 will be the first instead of January 2014).

                • 5. Re: Filter for 12 months ago
                  Joel Acha

                  Something along the lines of:

                   

                  DATE  '@{pCalendarYear}{2015-12-30}'

                   

                  should work.  As long as the presentation variable is being populated by an actual date column in the dashboard prompt, then you're guaranteed to get a "string" value in the right date format. I have used this approach before and it does seem to work.

                  • 6. Re: Filter for 12 months ago
                    Gianni Ceresa

                    Yes, something in that format (numeric-like year, month, day) is easy to convert to a date, it's just the full text version the OP posted ("December 2014") which is a nightmare and must be banner (except if thanks to a proper time dimension next to it there is a nice formatted column with a "normal" date or something close to it).

                    • 7. Re: Filter for 12 months ago
                      2885278

                      For both @Gianni Ceresa and @JayZee I would say thank you for sharing idea and solution,,I will try it first..thansk a million guys

                      • 8. Re: Filter for 12 months ago
                        Gianni Ceresa

                        2885278, after your tests come back here and post your solution as this is how a community forum is supposed to work (sharing your findings and solutions)

                        • 9. Re: Filter for 12 months ago
                          2885278

                          Sure @Gianni Ceresa

                          • 10. Re: Filter for 12 months ago
                            User784791-Oracle

                            I would suggest to create hidden prompt with variable prompt and set data type as date and time.

                             

                            this variable value can be refreshed from time table based on user selection.

                            refer http://docs.oracle.com/cd/E21764_01/bi.1111/e10544/prompts.htm#BIEUG1171 for more detail on hidden prompt.

                             

                            Thanks,

                            Ajay

                            • 11. Re: Filter for 12 months ago
                              2885278

                              to @Gianni Ceresa I did as what you said like "And I'm almost sure that in your time dimension you have a column working find with To_DateTime (you must have something like 2014-12 or 2014.12 or just "month number", and with "year" you can concatenate, to sort your months or April 2014 will be the first instead of January 2014)" and the result was working out that I used a column from time dimension like 2014-12 but in dasboard prompt it seems not what User want actually because User want something like "December 2014" when they want to choose Month. is it possible that in dashbord prompt will display "December 2014" but the value kept in presetation variable is like "2014-12"?

                               

                              Thanks all for sharing ideas, solutions and advices.with respectfully.