3 Replies Latest reply: Sep 27, 2017 6:37 PM by Carlos Enrique Maldonado Corea RSS

    QlikSense Join two dates by Common Calendar not working

    Carlos Enrique Maldonado Corea

      Hi everyone,

       

      Working on a model where we need to compare the balance of the accounts vs a set goal per month, i followed the tutorial here : Linking to two or more dates Pretty awesome by the way, but i'm not getting the desired output, it looks like it does not link the dates at all.

       

      This is the diagram: (Blues as Calendars, Gray is Fact table, Green is Set Goals, Purple is DateBridge between Facts and Set Goals, Yellow a Catalog)

      DG.png

      This is how a create the Calendars:

      Calendars.png

      These are the outputs: As you can see i'm getting arround tree times the value on each month.In the first one i use the Common Date Month Dimension and the Meassures are:

      SALDO Sum({<TipoFechaPrestamo={'Cartera'}>}SaldoPrestamo)

      META    Sum({<TipoFechaPrestamo={'MetaCartera'}>}Cartera)

      Outputs.png

      What i'm doing wrong?

      rwunderlich

       

      Here's is the app.

        • Re: QlikSense Join two dates by Common Calendar not working
          Justin Dallas

          Hello,

           

          What I would do if possible, is make change my load statements to copy and qualify the CommonDate field. Something like this.

           

          FechasPresetamosDateBridge:

          Load

              CommonDate AS 'FechasPresetamosDateBridge.CommonDate',

              FilialCategoriaKey...

              ...


          CommonCalendar:

          Load

              CommonDate AS 'CommonCalendar.CommonDate'

              Year...

              ...

           

          Then I would create a Table in the UI with the following columns

           

          Timestamp(FechasPresetamosDateBridge.CommonDate') | Timestamp(CommonDate) | Timestamp(CommonCalendar.CommonDate')



          Then, you will be able to see where your key values are failing.  I've noticed that when I join on dates, I generally have to make sure I have 'DayStart(someDate)'.  Otherwise, my dates won't link up, and it can take a very long time to figure out why.