- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Handling Missing weeks in chart
I've a country data(eg ABC) for which Weeks 12, 13, 14 and 17 are missing from Source data.
When I am trying to create a chart with weeks as dimension, It is not displaying those missing weeks.
Requirement - Is it possible to display data for missing weeks as 0 in chart?
Regards,
Sumit
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Sumit2504, to show that weeks they have to be loaded as a value, usually is a best practice to have an independent table to use as master calendar, this will covre all dates since the min date found in data to the end.
Data tables will only have date field, the other related fields from that dates (week, month...) will be on master calendar table.
You can crete a master calendar as:
Calendar:
LOAD
IdDate,
Date(IdDate) as Date,
Month(IdDate) as Month,
Num(Month(IdDate)) as MonthNum,
Year(IdDate) as Year,
Date(IdDate, 'YYYY/MM') as Year_Month
;
LOAD
MinFecha + IterNo()-1 as IdDate
While MinDate + IterNo()-1 <= MaxDate
;
LOAD
Floor(Min(FieldValue('IdDate', RecNo()))) as MinDate,
Floor(Max(FieldValue('IdDate', RecNo()))) as MaxDate
AutoGenerate FieldValueCount('IdDate');
// IdDate is the field name of the date in your data table
Still maybe you need to add +Sum({1}0) to your expression to show all weeks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Ruben,
Thanks for your response, I've already created master calendar but it won't work for me.
Additionally, I can see my missing values by adding +Sum({1}) to my expression but this will display all data and it will not change if I select any filters. it's function is as same as Show all values in dimension tab.
So this solution will not work for me.
Regards,
Sumit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Sumit2504 why you say the calendar won't work for you?
In the expression instead of {1} you can use other syntax but it will depend on your data and possible selections, if you upload a sample I can take a look.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
have you tried setting suppress missing and suppress 0 to off under presentation?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @rubenmarin ,
It's a client data so it'll be difficult for me to share.
I've two fact table - 1 is for transaction and another of historical. Both having IdDate as date field. So here the above mentioned master calendar script is not working.
Instead of {1} I tried with other identifiers but it's not working.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @rubenmarin
After creating master calendar, I can see my missing weeks as excluded value(Grayed cell) in a list box. How I can show these excluded weeks in Line chart ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
in the axis tab, set CONTINUOUS to ON
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
it will show the missing weeks but no data point will be plotted on missing weeks but consecutive data points will be connected
- « Previous Replies
-
- 1
- 2
- Next Replies »