Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
hlauerhaas
New Contributor

Dates from two tables as one dimension

Hello together,

I have three tables with data from SAP (the data model is much bigger, but these 3 tables are relevant):

position
VBEP.VBELN_POSNRforeign key schedule line
LIKP_LIPS.VGBEL_VGPOSforeign key delivery
VBAP.NETWR_perPiecenet value per single piece

schedule line
VBEP.VBELN_POSNR
VBEP.ETENRschedule line no.
VBEP.LDDATdelivery date (plan)
VBEP.LDDAT_YearMonth
VBEP.BMENGconfirmed quantity

delivery
LIKP_LIPS.VGBEL_VGPOS
LIKP_LIPS.WADAT_ISTdelivery date (actual)
LIKP_LIPS.WADAT_IST_YearMonth
LIKP_LIPS.LFIMG

Now I want to build a pivot and a bar chart showing the netvalue for planed delivery date and actual delivery date.

My idea was to create an idependent calender with all dates of VBEP.LDDAT and LIKP_LIPS.WADAT_IST combined in one column and use this as dimension. But at creating the first formula as "sum({$<LIKP_LIPS.WADAT_IST = {"[Date from the independed calender"} >} VBAP.NETWR_perPiece * LIKP_LIPS.LFIMG)" I learned that this will not work because the calculation is not been done after each line.


Another idea was to create a table like this:


calender
independent_date
VBEP.LDDAT
LIKP_LIPS.WADAT_IST


But then I would get a circular reference.


Has anybody a solution for that. I have allready search the forum (perhaps with the wrong keywords ;-)) and did not find a solution fitting my problem.


thanks in advance for answering,

Heike

1 Solution

Accepted Solutions
chrismarlow
Valued Contributor

Re: Dates from two tables as one dimension

Hi Heike,

I think there are a few options depending on the rest of your requirements.

Example attached uses the very beginnings of a disconnected master date calendar (that I think there are lots of write ups on how to take further, key being is not linked) & using Sum & If in the fields.

318179.png

Let me know if that helps.

Cheers,

Chris.

6 Replies
chrismarlow
Valued Contributor

Re: Dates from two tables as one dimension

Hi Heike,

I think there are a few options depending on the rest of your requirements.

Example attached uses the very beginnings of a disconnected master date calendar (that I think there are lots of write ups on how to take further, key being is not linked) & using Sum & If in the fields.

318179.png

Let me know if that helps.

Cheers,

Chris.

Re: Dates from two tables as one dimension

maybe helpful:

Canonical Date

regards

Marco

hlauerhaas
New Contributor

Re: Dates from two tables as one dimension

Hello Chris,

Thank you very much. This works fine for my issue and is a really simple solution wich is not destroying my data model. I could have come up with that myself, but sometimes you not see the forest for the trees.

Thanks,

Heike

hlauerhaas
New Contributor

Re: Dates from two tables as one dimension

Hello Marco,

I read this blog before, but it doesn't fit. I already tried it and got circular references.

Thanks,

Heike

chrismarlow
Valued Contributor

Re: Dates from two tables as one dimension

Thank-you Marco - that is what I was thinking of.

Re: Dates from two tables as one dimension

Keep in mind that both island tables and the usage of if() functions in your expressions most likely will cause performance issues in large data sets.