6 Replies Latest reply: May 1, 2009 5:55 PM by badboy81 RSS

    YearMonth chart with macro and set analysis which shows also missing data, is it possible?

       

      Hi,

      I have tricky problem with YearMonth Chart! You can select a time period via macro for example Jan-2007 to Dez-2007 and save it via macro to a variable. The expression in the chart includes the time range from the variable, and a modifier which says take only data from Type = A . But there is my problem, than for the months in the year 2007 for Aug, June and April there is no Data of the Type A, but I want that the chart shows me also the missing bars for this months with null as sum.

      Is it possible to solve this problem and when yes please give me the solution!

      I have prepared an adequate sample where you can try to solve it.

      Thanks for helping me!

        • YearMonth chart with macro and set analysis which shows also missing data, is it possible?
          Oleg Troyansky

          Herbert,

          I'm not sure that I understand what you are trying to do with your dates, but the problem (as I see it) is because you Date1 and Date2 are both linked to the Type. When there are no records for a specific Type, Date2 is not "available" and therefore it doesn't make it to the selection list within Group1.

          If you detach Date2 into a separate list, not related to the Type, you should be able to achieve what you are trying to achieve.

          Oleg

            • YearMonth chart with macro and set analysis which shows also missing data, is it possible?

              Thanks for answer, I have tried to make something like Oleg said, but I came to no solution, maybe you Oleg can take my sample application and make some changes to it, that will be great.

              And to explain my problem in short form, my endresult which I want to get is a dynamic chart, I tried it before with a complex set analysis function, you can see it as second expression in the formula at the chart, and now I tried it with macro and variables and with both I had the same problem.

              Yes there is no data for the specific Type not every MonthYear has a Type A and not every a Type B, In my real application I also have a calendar. So all the dates exist (days, weeks, years, months, YearMonths....) also where no data exists.

              I think the way I am trying to solve my problem is not right, all i want is a dynamic chart for example when I select a month I would get the data of the last 12 months, ==> selection of Nov-2007 than the chart should include a date range from Dez-2006 to Nov-2007, the bars for every month should always be there (12 bars) and if there is no data there should be 0 when I use a count or a sum function.

              Or is there a solution how I can change the sample data in a way so that qlikview recognizes that there is null as result also without real data, or should I add in some way any extra data fields?

              I hope I can get some suggestions?

              The application is in my first post you can download it.

              Kind regards,

              Herbert

              • YearMonth chart with macro and set analysis which shows also missing data, is it possible?

                Hi Oleg,

                Thank you very very much, I like your solution and the best it is without macros and set analysis and I can write the motifications directly within the if formula in the chart, like Type = 'A'

                But maybe you can give me some further input about the formula in the dynamic dimension.

                =if(num(CalDate+365) > num(Date3) and num(CalDate) <= num(Date3), date(CalDate, 'MMM-YYYY'), null())

                Can you explain me in short form what the formula does in every part, how it comes to the lasts 12 months, and how I can use it when I would like to have other date ranges lets say the last 3 months or the last 6 months, so I can use it in differnt form, I think I have to change the +365 but how?

                Thanks Oleg it works fine.

                Kind regards

                Herbert