4 Replies Latest reply on Sep 15, 2016 1:07 AM by Katarina Day

    Date Label as parameter

    Katarina Day

      Hi,

       

      I am starting to tear my hair out to work this out , so before i break my PC I thought I ask on here if anyone can help.

       

      I am trying to create a parameter that would contain week numbers and I need to connect 2 dates (for 2 different data thats why I need them both in) and also those 2 dates are calculated for the same week last year to it.

      Basically i am comparing week on week this year and last year and users would like the option to choose their week.

       

      Example:

      Week Number          2016 Registrations              2015 Registrations          2016 FTDs           2015 FTDs           (Week Label Filter available to pick the week)

      1                                        15                                             11                             11                         9

      2                                        16                                             12                             11                        10

      3                                        17                                             15                             10                         7

      etc...

       

       

      Please if anyone know how I can get this filter for both dates this year and last year.....

       

      Hopefully it makes sense .

       

      thank you

        • 1. Re: Date Label as parameter
          David Li

          Hi Katarina! You can try creating a parameter that accepts integers from 1 to 52. Then, use something like this as a filter:

          DATEPART('week', [The Date]) = [Week Parameter]

          I haven't tested this code, but something close to this should work.

          • 2. Re: Date Label as parameter
            Katarina Day

            Hi David, thank you

            I have tried that. The problem I am having is that I have 2 dates: FTD Date and Registration Date and I need to calculate week numbers for 2015 and 2016 from both so I need to connect 4 dates to the parameter (hope it makes sense). I am not sure how to calculate the week number for last year so it matches this year week number. Then also I have measures, 1 per FTD date and 1 per Registartion and i need to count distinct them for each date. Hope it makes sense .

            • 3. Re: Date Label as parameter
              David Li

              Can you post a packaged workbook? It's a little hard to understand without seeing it.

              • 4. Re: Date Label as parameter
                Katarina Day

                Hi have attached workbook and excel with sample data. contains 2 dates FD date and reg date and sample IDs. So what i need to create is week number parameter that is connected to both dates and then calculate from IDs unique number of IDs that registered in that week and unique numbers of IDs that had first deposit. So it should look like this:

                 

                Week Number                Reg               FTDs

                1                                      12                  11

                2                                      11                  10

                etc. with the filter that shows week numbers (i have more data to put in the report but once i work this out i can easily copy the rest).

                 

                 

                .