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

    QlikSense Join two dates by Common Calendar not working

    Carlos Maldonado

      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.

            • Re: QlikSense Join two dates by Common Calendar not working
              Carlos Maldonado

              I tried with DayStart() in the dates that i use to join the Facts with the set Goals but the dates are fine. What i can see thanks to your advice of listing the fields on a table is that in the Tutorial where i took the inspirations, the dates where there is no data they have the DateType field on Blank buy in my case they have a Value Type, that's the reason i think i'm getting the false relation y triple the ammounts of the Balance and the Goals. But i dont know why