Duplicate data - Calendar day week

[luizcdepaula]

Hello experts,

I am facing a challenge that it seems I cannot solve it. I am combining two data sets that have different time dimensions, one is in weeks, which includes sales forecast, and the other is in days, which includes sales. I am combining the two data sets based on a few dimensions, such as Material ID and Plant, and date. I am converting the week into a date as well.

Because some weeks are split into two months, the week numbers are listed twice, one for each month it belongs to. This is causing the sales numbers to duplicate. Find example below.

I am using the peek function specifying that if Calendar Week/Year is the same in the next row but the Calendar Month/Year is different, then Sales equal zero.

if("%Calendar Year/Week"=peek("%Calendar Year/Week")and "%Calendar Year/Month"<>peek("%Calendar Year/Month"),0,

        "Actual Gross Cases") as Sales_peek

This is causing the duplicates to be 0 for both rows, not just the one that is duplicate. I still the first value to show.

Any suggestions?

Thanks in advance,

LD

[sunny_talwar]

Would it be possible to share a sample to look at the issue?

[luizcdepaula · Accepted solution]

Sunny,

The way I was able to solve the issue was to concatenate instead of outer join. I did not want to concatenate initially to avoid adding extra rows, but that was the only way. It kept the original measure instead of trying to apply Peek function and create another measure. Find a screenshot below with the new data.

Thank you for your interest.

LD