Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luizcdepaula
Creator III
Creator III

Duplicate data - Calendar day week

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

1 Solution

Accepted Solutions
luizcdepaula
Creator III
Creator III
Author

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

View solution in original post

2 Replies
sunny_talwar

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

luizcdepaula
Creator III
Creator III
Author

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