Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
hlauerhaas
Contributor
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
Specialist II
Specialist II

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.

View solution in original post

6 Replies
chrismarlow
Specialist II
Specialist II

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.

MarcoWedel

maybe helpful:

Canonical Date

regards

Marco

hlauerhaas
Contributor
Contributor
Author

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
Contributor
Contributor
Author

Hello Marco,

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

Thanks,

Heike

chrismarlow
Specialist II
Specialist II

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

MarcoWedel

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.