topic Re: Handling Missing weeks in chart in QlikView

Handling Missing weeks in chart

Sumit2504 — Fri, 15 Nov 2024 23:16:23 GMT

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

Re: Handling Missing weeks in chart

rubenmarin — Fri, 22 Jan 2021 08:37:38 GMT

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

Re: Handling Missing weeks in chart

Sumit2504 — Fri, 22 Jan 2021 10:08:16 GMT

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

Re: Handling Missing weeks in chart

rubenmarin — Fri, 22 Jan 2021 16:57:21 GMT

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.

Re: Handling Missing weeks in chart

edwin — Fri, 22 Jan 2021 23:04:33 GMT

have you tried setting suppress missing and suppress 0 to off under presentation?

Re: Handling Missing weeks in chart

Sumit2504 — Sun, 24 Jan 2021 02:18:31 GMT

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.

Re: Handling Missing weeks in chart

Sumit2504 — Sun, 24 Jan 2021 02:29:02 GMT

Hi @edwin ,

I've tried all expected options under presentation and dimension tab but no luck

Re: Handling Missing weeks in chart

Sumit2504 — Sun, 24 Jan 2021 15:19:57 GMT

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 ?

Re: Handling Missing weeks in chart

edwin — Sun, 24 Jan 2021 19:32:01 GMT

in the axis tab, set CONTINUOUS to ON

Re: Handling Missing weeks in chart

edwin — Sun, 24 Jan 2021 19:34:20 GMT

it will show the missing weeks but no data point will be plotted on missing weeks but consecutive data points will be connected

Re: Handling Missing weeks in chart

Sumit2504 — Mon, 25 Jan 2021 01:09:37 GMT

Hi @edwin ,

This solution won't work for me as it will disable the x-axis scrollbar.

Is there any other way where I can see my missing values with x-axis scrollbar.?

Re: Handling Missing weeks in chart

rubenmarin — Mon, 25 Jan 2021 07:46:15 GMT

Hi @Sumit2504, that wouldn't matter for the calendar if the link between those table is only IdDate... if there are other fields it will create a circular error, usually it is solve concatenating transactional and historical data so you can end with only one fact table instead of two.

Re: Handling Missing weeks in chart

rubenmarin — Mon, 25 Jan 2021 08:01:32 GMT

Hard to say without having taking a look to data model, it the weeks are greyed out it's because some other selections are excuding them.

Anyway I would try something like [YourExpression]+Sum({1<IdDate={">=$(=Min(IdDate))<=$(=Max(IdDate))"}>} 0)

It maybe needs some fix to adapt to your data model, date format or possible selections.

Other option as @edwin says is to use continuous x axis, for this the dimension should be in number format.

Re: Handling Missing weeks in chart

Sumit2504 — Wed, 27 Jan 2021 10:56:07 GMT

Hi @rubenmarin ,

I tried the expression mentioned by you and @edwin. It does display with the line going to 0 properly. However, the chart shows ALL weeks and it'll deselect the range I've selected. So this won't work.

Re: Handling Missing weeks in chart

edwin — Wed, 27 Jan 2021 16:44:42 GMT

attaching a QVW will make this quicker

Re: Handling Missing weeks in chart

Sumit2504 — Thu, 28 Jan 2021 04:57:54 GMT

Hi @edwin , I am not able to see attached file, please reattach again

Re: Handling Missing weeks in chart

edwin — Thu, 28 Jan 2021 14:57:24 GMT

i was not clear.
i was suggesting if YOU attach a QVW, this process will go quicker
is it obvious we are missing something and looking at the same thing, communication will be clearer

Re: Handling Missing weeks in chart

rubenmarin — Fri, 29 Jan 2021 07:36:46 GMT

Hi @Sumit2504, if it's showing all dates it's because this: "Sum({1<IdDate={">=$(=Min(IdDate))<=$(=Max(IdDate))"}>} 0)", as I said in previous post "needs some fix to adapt to your data model, date format or possible selections".

Probably you don't have an IdDate field and you ned to use your date field, and the Min/Max may be converted to your date format, also other selections on the app may be take into account.

This could be quicker if you upoload a sample, that's not your client data, it's some data created by you that replicates the issue so we can test.