QlikSense Join two dates by Common Calendar not working

[Anonymous]

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)

This is how a create the Calendars:

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)

What i'm doing wrong?

rwunderlich‌

Here's is the app.

[JustinDallas]

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.

[Anonymous]

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

[Anonymous · Accepted solution]

I found out the problem, My FilialCategoriaKey was unique enough, i ended making a unique key by adding the date of transaction to FilialCategoriaKey (FilialCategoriaKey + Tranasction Date = FilialCatetoriaFechaKey) on both fact tables Prestamos and MetasCarteraPrestamos and by using this new key at the Common calendar the problem was solved.

Thanks Justin for the helping find the inside.