Dates from two tables as one dimension

[hlauerhaas]

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_POSNR	foreign key schedule line
LIKP_LIPS.VGBEL_VGPOS	foreign key delivery
VBAP.NETWR_perPiece	net value per single piece

schedule line
VBEP.VBELN_POSNR
VBEP.ETENR	schedule line no.
VBEP.LDDAT	delivery date (plan)
VBEP.LDDAT_YearMonth
VBEP.BMENG	confirmed quantity

delivery
LIKP_LIPS.VGBEL_VGPOS
LIKP_LIPS.WADAT_IST	delivery 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

[chrismarlow · Accepted solution]

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.

Let me know if that helps.

Cheers,

Chris.

[MarcoWedel]

maybe helpful:

Canonical Date

regards

Marco

[hlauerhaas]

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]

Hello Marco,

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

Thanks,

Heike

[chrismarlow]

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.