22 Replies Latest reply: Oct 19, 2012 3:07 PM by John Witherspoon RSS

    Missing values on time dimension

    Florian Pennetzdorfer

      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").

       

      screenshot.jpg

      Thank you for your help!

        • Re: Missing values on time dimension
          Felim Shanaghy

          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

            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
                Florian Pennetzdorfer

                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")?

                screenshot1.jpg

                 

                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)

                screenshot3.jpg

                 

                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

                    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")?

                    screenshot1.jpg

                     

                     

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

                    thanks!

                    • Re: Missing values on time dimension
                      whiteline _

                      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

                          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 _

                              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

                                  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 _

                                      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

                                          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
                                            Carlo Pazzaglia

                                            Hello

                                             

                                            Just to thank you!!!

                                             

                                            I have tried your solution and it works fine

                                             

                                            Best regards

                                             

                                            Carlo

                              • Re: Missing values on time dimension
                                Pertti Lahtinen

                                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
                                    Carlo Pazzaglia

                                    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

                                     

                                    BR

                                    Carlo

                                      • Re: Missing values on time dimension
                                        Pertti Lahtinen

                                        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
                                            Carlo Pazzaglia

                                            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
                                                John Witherspoon

                                                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
                                                    Carlo Pazzaglia

                                                    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
                                                      Carlo Pazzaglia

                                                      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....

                                                       

                                                      Br

                                                       

                                                      Carlo

                                                        • Re: Missing values on time dimension
                                                          John Witherspoon

                                                          OK, I've tried to simplify as much as possible in the attached example.  I've eliminated the "Turn" part since most people will be going by dates most of the time.  I now load the Calendar and DateLinks tables first and without any explicit regard to the dates available in my data source, as would be typical in my applications.  In the script, I've eliminated all the WHILE loops and variables and iterno() and pick() stuff and tried to stick with the basics.  I show the full, resulting Data table with its 'All' keys and the DateLinks table for the selected dates.  This is still fundamentally a rather complicated solution to the problem, but hopefully I've eliminated any unnecessary complexity.

                                                • Re: Missing values on time dimension
                                                  Graeme Smith

                                                  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