11 Replies Latest reply on Jul 3, 2018 8:49 AM by Ganesh Vijay Kumar

Calculating Missing weeks in data!

Hello Experts,

I am trying to calculate the missing weeks in the data. Below image shows how is my existing data.

The below image shows what is expected results:I am trying to calculate "Calculated Calendar Week" and "Calculated week Diff"

Please let me know if this is achievable. Any help in achieving the desired results would be deeply appreciated.

Thanks,

Ganesh

• 1. Re: Calculating Missing weeks in data!

Hi Ganesh,

Given that your week numbers are simply integers, you should be able to use a lookup function with previous_number() to generate the missing numbers and to return zero for these missing weeks in two separate calculations

Steve

• 2. Re: Calculating Missing weeks in data!

see below

right click the date pill and select show missing dates

then yu can use the table calc to get the differenc

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 3. Re: Calculating Missing weeks in data!

Hi Jim,

Show missing values is only relevant for when a full date is presented, and, will only work with dates, no other values will work regardless of pattern-similarity.

In this instance, Ganesh's data is only presenting integers representing weeks with no actual dates included in the set, so this method will not work; which is why I chose not to mention it here.

Steve

1 of 1 people found this helpful
• 4. Re: Calculating Missing weeks in data!

Thanks a lot for the response Jim and Steve.

Steve, Yes you are right the dates or weeks are not available in the original dataset itself for the missing lines. As mentioned in my post, If there is a missing week, I just need to get it and measure value for that missing week could be zero.

Please let me know if this is possible.

Thanks,

Ganesh

• 5. Re: Calculating Missing weeks in data!

Hi

IF there is not record at all in the data set for the missing week you can create a file of dates "New Dates" that  includes every week ( i.e. not missing weeks) in a column

Add it into the tableau and JOIN if with your current data set on date - Use the "new dates" in your viz and bring the values in using a ZN() function to 0 out the null values

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• 6. Re: Calculating Missing weeks in data!

Hi Jim,

This won’t be straight forward join, since I am using Snapshot data. Let me try to explain it again

I am working on forecast data, customers are sending us forecast every week for future dates. Since they are sending the forecast every week the data in the ERP system we use gets overwritten. Hence are capturing the snapshots every week.

In the below example:

if we consider my current week is 201826 and let’s see how we calculate Weekdiff.

•   0 = Current week demand (201826 – 201826 = 0)

•   1= 1 week before demand for week 201826 is 2400 (201826-201825 = 1)

•   2= 2 weeks before demand for week 201826 is 2400 (201826-201824 =2)

•   3= 3 weeks before demand for week 201826 is 2400 (201826-201823 =3)

•   4=  4 weeks before No data available in ERP (201826-201822 =4 )   : Since there is not data here I am trying to create a calculated field and get 201822 and 4  and measure value could be either 0 or previous weeks value

•   5= 5 weeks before  No data available in ERP (201826 – 201821 = 5 : Since there is not data here I am trying to create a calculated field and get 201821 and 5 and measure value could be either 0 or previous weeks value

•   6=  6 weeks before demand for week 201826 is 2400 (201826-201820 =6)

Calendar Week

Snapshot Calendar Week

Week Difference

Demand

201826

201820

6

2400

201823

3

2400

201824

2

2400

201825

1

2400

201826

0

2400

201827

201821

6

2400

201822

5

0

201823

4

0

201826

1

2400

201827

0

2400

Hope this avoids the confusion.

Thanks,

Ganesh

• 7. Re: Calculating Missing weeks in data!

OK

the principle is the same - you need to create a framework to hang the data on (join)

the framework needs to include each "date value"  you expect from the customer format is not important - you just need a place in the framework to the "Date Value" indicators that will be populated with a value or null

the calculation is a different story - still sounds like a table calc - they just do a lookup and take the current value in the tabel from the previous

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 8. Re: Calculating Missing weeks in data!

Hi Jim,

Based on your suggestion I created a new dataset and tried to join. I am using Hana live connection as primary data source. Got to know that cross data join(Full outer Join) for Hana and any other data source do not work.

Thanks,

Ganesh

• 9. Re: Calculating Missing weeks in data!

Didn't know that - will it let you left joint using the new frame work as the left file?

1 of 1 people found this helpful
• 10. Re: Calculating Missing weeks in data!

Hi Jim,

The join worked now and I have the missing data, I had to select “Null” also in the filters I used.

But now I encounter another problem when I am trying to write a calculated field.

I have two data Sources.

One is snapshot data where I get my demand from as shown in the image below. The two lines highlighted in red color is missing in the original dataset. Hence I have created a separate dataset with all the weeks and did a join as suggested by you.

Calendar Week

Snapshot week

Week Diff

Demand

201826

201820

6

2400

201826

201821

5

0

201826

201822

4

0

201826

201823

3

2400

201826

201824

2

2400

201826

201825

1

2400

201826

201826

0

Another one is booking data:

Calendar Week

Order Quantity

201826

2400

I have to blend Calendar week in both the tables and get the Order quantity in Calendar week 201826 and calculate the variance for each week demand Versus Order quantity.

Below table shows the expected results:

Calendar Week

Snapshot week

Week Diff

Demand

Booking

Variance

201826

201820

6

2400

2400

100%

201826

201821

5

0

2400

0%

201826

201822

4

0

2400

0%

201826

201823

3

4800

2400

200%

201826

201824

2

1200

2400

50%

201826

201825

1

2400

2400

100%

201826

201826

0

2400

I wrote the mentioned formula after blrnding Calendar week.

IF ATTR() = 0 THEN SUM(.[Order Quantity])

ELSE SUM(ZN()) END

Below are the results under Column named Final Value (Booking)

In the above image “line deleted in the system”  are missing weeks and I am getting 0 for that particular line instead of 2400.

How do I have it fixed and get 2400 on each line as expected.

Your help will be deeply appreciated.

Thanks,

Ganesh

• 11. Re: Calculating Missing weeks in data!

Attached is the sample Twbx file for your reference.

Instead of null I want the Order quantity Number