topic Re: Missing values on time dimension in QlikView

Missing values on time dimension

pennetzdorfer — Tue, 24 Jul 2012 14:27:36 GMT

Hey,

We want to show sales in a bar chart diagram with monthly intervals on the X-axis. Therefore we implemented a master calendar which allows generally to display all months of a year, even when a specific month has no values (having unchecked "Suppress Zero-Values" in the Presentation tab).

Nevertheless, when we select a region which doesn't have values for a certain month, this month isn't shown anymore (e.g. May 2011 for Region "a").

Thank you for your help!

Re: Missing values on time dimension

Tue, 24 Jul 2012 14:32:10 GMT

You would not see May 2011 if there is no resulting data attached to it, in the above instance I am assuming your expression is 0 for that month, ensure you have unchecked the "supress null" tickbox

Re: Missing values on time dimension

Tue, 24 Jul 2012 18:41:58 GMT

Deselecting "supress null" will work only when no selections are made. Once region a is selected then may 2011 will be filtered out because there is no data with region a and therefore will not be displayed in the chart.

You can try one of the following:

1) Select show all values. All dates will be displayed regardless of the selection.

2) Add a month start date to the master calendar, change the chart to use the month start date as the dimension, change the x-axis to be continous, and format the axis to MMM-YYYY. You will need to fiddle with the min, max, and step values on the x-axis to make it display correctly.

3) Add rows to the data table to ensure that there is a data for every month. You should also have a data type column to identify these fake rows. Use nulls or 0 (it will depend on your calculations) for the attribute columns of the fake rows. Change the expressons in your chart to always include these fake rows.

Re: Missing values on time dimension

pennetzdorfer — Wed, 25 Jul 2012 08:32:53 GMT

Thanks vhuynh for your suggestions!

ad 1) "Show all values" is almost the solution we are looking for. There's just one problem: when we select year 2011 then we also see the months of 2012 on the X-axis.

That's a disadvantage when having several years. Is there a way to just show the selected years (e.g. in tab "Dimension limits")?

ad 2) We've already tried this option but can't figure out how to set the step values correctly (at the moment intervals depend on size of the bar chart)

ad 3) Our attachment above is just an example. Normally we have much more selection options and more data shown in various years. Once a selecion option (e.g. region, dealer, ...) is selected the other dates are filtered out. How to write in the script that it automatically recognizes the dates missing and sets a null for the selected options, so that all the dates are shown on the x-axis?

Anyway we would prefer a solution in the layout rather than the script.

Thanks!

Re: Missing values on time dimension

Tue, 11 Sep 2012 07:17:03 GMT

Florian Pennetzdorfer wrote:
Thanks vhuynh for your suggestions!
ad 1) "Show all values" is almost the solution we are looking for. There's just one problem: when we select year 2011 then we also see the months of 2012 on the X-axis.
That's a disadvantage when having several years. Is there a way to just show the selected years (e.g. in tab "Dimension limits")?

I'm also interested in this issue. Does anyone have an advice for it?

thanks!

Re: Missing values on time dimension

whiteline — Tue, 11 Sep 2012 07:41:30 GMT

The problem is that when you select your region there is no data associated with May 2011.

Not only Amount but also even Dates.

You can add that data in the script.

All missed months for each product with null() Amount.

I think your real data is sufficiently complex that there are more values than nulls.

Re: Missing values on time dimension

Wed, 12 Sep 2012 07:53:54 GMT

Sure it sounds like a fair option.

But code-wise, how would that look?

Where YYYYMM IsNull .... ??

And I should also limit the creation of YYYYMM, as I wouldn't like to have entries dating back at French Revolution times!

Re: Missing values on time dimension

whiteline — Wed, 12 Sep 2012 08:10:35 GMT

Why?

You want see the nulls on chart.

To see them, they must be there.

So you have to create them in script or synthetically.

If you want to see nulls 'back at French Revolution times' you have to create them somehow.

Re: Missing values on time dimension

Wed, 12 Sep 2012 08:18:29 GMT

whiteline wrote:
Why?
You want see the nulls on chart.
To see them, they must be there.

Yep, already agreed on that!

So you have to create them in script or synthetically.
If you want to see nulls 'back at French Revolution times' you have to create them somehow.

That I understood; could you please point out how? I'm a beginner at scripting and I cannot find a creative yet functional way to do this...thank you!

Re: Missing values on time dimension

whiteline — Wed, 12 Sep 2012 09:53:32 GMT

Something like this:

FullRegionsPeriods:

LOAD distinct

Region

Resident FactTable;

Preiods:

LOAD distinct

MonthYear

Resident FactTable;

left join(Regions)

LOAD * RESIDENT Preiods;

drop table Preiods;

Re: Missing values on time dimension

Wed, 12 Sep 2012 14:09:49 GMT

thank you, but it is returning 4 errors if I add the script to the example above

Table not found

FullRegionsPeriods:

LOAD distinct

Region

Resident FactTable

Table not found

Preiods:

LOAD distinct

MonthYear

Resident FactTable

Table not found
left join(Regions)
LOAD * RESIDENT Preiods

Table not found

DROP TABLES statement

In all honesty, I bumoped into this topic but my environment is a bit different from the OP, I think.

I am just dumping data from a server and, regardlessly from the item selected, I would like to see the month in the graph even if sales were 0 for that particular item.

Sure indeed, the month is present in the table, just not associated with all the item I can select.

I will try to brainstorm a bit about it, thank you for your help so far

Re: Missing values on time dimension

Anonymous — Fri, 12 Oct 2012 08:35:13 GMT

Hey

You can use SetAnalysis to generate dummy entries on X-axis.

If you have a lot of dimensions, using variable is recommented.

Please study the attached document.

Re: Missing values on time dimension

kaicpa2 — Sun, 14 Oct 2012 02:04:56 GMT

Hello

I'm sharing the same problem so I'm pretty intresting in the solution

The SetAnalysis stuff is great solution but I think that the sample in MasterCalendar is not working.

When selecting region A , May 2011 data disappear from the chart, and if I understood correctly it is what we want to avoid.

Could you please check it out and give us direction?

Thanks a lot for your help

Carlo

Re: Missing values on time dimension

Anonymous — Mon, 15 Oct 2012 06:34:53 GMT

Hello Carlo

True, data disappers because there is no data in the fact table on May.

What would you except to see when you select May 2011?

Best Regards

Pertti

Re: Missing values on time dimension

kaicpa2 — Mon, 15 Oct 2012 08:04:24 GMT

Hello Pertti

First of all thanks to the reply.

I would need to have ie May 2011 shown with 0 even if there is no value for that month.

I am personaly using a similar graph to see the consumption of a certain component. The data are from a server every month and when there is no consumption for a given component that component is not included in the report

Sinceit is important to get at gllance that the consumption is 0 I would like it well shown in the graph otherwise look like that there is alwayse usage every month.

Actually I was in the understanding that the report you sent was already doing this!

Otherwise I do not understand the need of the SetAnalysis to generate dummy entries on X-axis:

With no selection on Region A, May 11 is well shown (0/empty value) but it is shown even if I cancel the additional variable vMakeTimelineContinuos

Thanks for the time you will dedicate on this

Best regards

Carlo

(I will offer you a lovely cup of coffee if you are coming around Milan)

Re: Missing values on time dimension

johnw — Tue, 16 Oct 2012 00:53:30 GMT

I'll admit to only skimming the page and not looking at the examples. But attached is one fairly-complicated way to handle it primarily with the data model. Complexity is the only practical drawback I'm aware of, and I've used this approach in one of our real applications, where it has been working just fine. Performance should be high, no new rows in the original table (Edit: requires new rows in the original table), charts don't display missing dates unless you tell them to. I can't take credit - Michael Solomovich gave me the necessary hints to work this out when I was having this same problem.

Practical cases may be simpler than this example. The calendar generation is much more complicated than in my typical applications due to breaking a day into three turns and basing the calendar on my data (where in a typical appliation, I load data based on the desired calendar). I think the things to really look at are the generation of the DateTurnKey in the original table, and the creation of the DateLink table.

It may be confusing why I lock ShowZeroPoints?='N', and then use set analysis to sum({<ShowZeroPoints?={'Y'}>} Sales). It might seem a bit contradictory, and a giant waste of effort. The idea is that most charts don't want anything special. They just want our original data displayed the way QlikView always displays it. Locking the field to N provides exactly that, and means that normal charts don't need to use any set analysis to exclude our extra data. It'll be excluded automatically by the locked field. Only charts that need to show the zero points need to use set analysis, and only to say that they want to show zero points. I have no other charts in the example, so it's a complete waste here, but I think it's an important part of the example if you're trying to apply it to real world cases.

Re: Missing values on time dimension

kaicpa2 — Tue, 16 Oct 2012 06:22:39 GMT

Hello John

Thanks for the example.

It is quite intresting and it is very good for my training 🙂

Yesterday evening I manage to solve the issue with the suggestion proposed by Whiteline (generating 0 entry) but I will try also your solution

Thanks a lot

Carlo

Re: Missing values on time dimension

kaicpa2 — Tue, 16 Oct 2012 06:24:40 GMT

Hello

Just to thank you!!!

I have tried your solution and it works fine

Best regards

Carlo

Re: Missing values on time dimension

Fri, 19 Oct 2012 15:40:40 GMT

This set analysis approach is a really good idea. In one scenario, we have a very large but sparsely populated data set with a lot of dimensions. All months would exist across the full data set, but won't be populated for all of the sub groups of dimensions. Bringing in dummy rows with zeros for all combinations doubled the QVW file size and increased the load time dramatically (we are pivoting the data too), so this set analysis approach is very efficient in this scenario for us. Great idea - thanks for sharing it!

Cheers,

Graeme

Re: Missing values on time dimension

kaicpa2 — Fri, 19 Oct 2012 17:37:46 GMT

Hi John

I have been heavly trying out your example as the "generating 0 entry" has a few side effect that, even if now do not bother me now, they will in future.

I must admit since I'm bit new that the "Turn" stuff complicates my life quite a bit as I have basically two field to take care of : Date (called period in the excel file source data and for unknow reason loaded QV as number - never mind) and a material number KM123 etc

do you have a simpler example you can share? or suggestions?

Feel free to send me to hell if I am asking to much....

Carlo