6 Replies Latest reply: Jul 22, 2011 12:18 AM by lawrencem RSS

    Set a custom dimension to show more than 12 month


      I'm kinda new to qlikview but I love it. I've searched the forum and google the hell out of me but I cannot seem to find the answer.

      I have loaded a xlsx file with colums:

      Month     Year     NSV     CM ....
      1             2009

      2             2009

      3             2010

      4             2011



      What I need to do is to in one diagram show how the CM (contribution margin) varies over time (month). To show this in a sensible way I'd like the dimensions to show all the years month in a row e.g.


      X-axel: 1,2,3,4,5,6,7,8,9,10,11,12,1,2,3,4,5,6,7,8,9,10,11,12 etc

      The first 1-12 is for 2009, the second is for 2010 etc.


      I've tried to set the expressions to specific years but it lacks pedagogical overview. Can someone please help. I'm doing an internship with way to much responsibility and to little time


      My experience in coding and java-like language are very limited, thus an educational explantion would be much appriciated.


      Best regards

      Robin Rendahl

        • Set a custom dimension to show more than 12 month
          Chris Cammers

          Add a field to your calendar table using this expression




          Date fields are special and contain a numeric value and the numeric equivalent so if you advance your detail date to the Month End Date then you will only get one value per month but since you will be using the dual properties of the date data type you will have the numbers 1-12 displayed but the actual values will be equivalent to the appropriate month ending dates.


          Good Luck



          • Re: Set a custom dimension to show more than 12 month
            Chris Cammers

            Oh, so you want to use the formula as a chart dimension.

            And I did not pay attention to your sample data, sorry about that.

            The easiest thing to do would be to add a date field to your spreadsheet, let's call it NSV_Date (my best guess based on your columns)


            then you would have in your load script





                 Date(MonthEnd(Date(DateField)),'M') as [Distinct Month]

            From ...


            Or you should be able to do this with the data you have, however this is more tricky and requires a calcualted dimension which I would not reccommend.


            Date(MonthEnd(Date(Num([Month] & '/1/' & [Year]))),'M')


            Hope this helps

            • Re: Set a custom dimension to show more than 12 month


                   As per my understanding of your query you just need to set your charts multi dimensions in proper order. PFA.

              The charts use Year & Month as dimensions, and the CM field as expression. Probably the second chart would be better, as it uses Month as primary dimension and year as secondary. This would also be better in UI perspective as the bars wont be crowded because the months will remain fixed i.e 12 months Jan-Dec, while the number of years would depend on your data. Incase, using Year as primary dimension, do set your Dimension limitations under Presentation tab of chart properties. Yeah, but do setup your calendar stuff first, incase you creating master calendar.

              Is this what you were looking for? Or have I completely misunderstood, do let me know.