6 Replies Latest reply: Feb 29, 2012 8:42 AM by Celambarasan Adhimulam RSS

    Group months in calendar

      I have loaded in my dashboard a calendar looking like this:

      Date          Quarter/Year    Year       Month/Year

      201112               Q1          2011        Dec-11

      201111               Q1          2011        Nov-11

      201110               Q1          2011        Oct-11

      And it works fine.

       

      Now the request is to have, in addition to all this, months groups like

      M1-2011 will have Jan11 + Feb11 + Mar11

      M2 -2011 will have Feb11 + Mar11 + Apr11

      M3-2011 will have Mar11 + Apr11 + May11

       

      And so on. Does anyone know how I can do this groups in qlikview?

       

      Thanks for the help,

       

      Joana

        • Group months in calendar
          Celambarasan Adhimulam

          Hi,

               Try with this

               Load * Inline

               [MonthGroups,Month

               M1,Jan

               M1,Feb

               M1,Mar

               M2,Feb

               M2,Mar

               M2,Apr

               ......

               ];

           

          Then you can control this in expression.

           

          Celambarasan

            • Group months in calendar

              Thanks, it worked perfectly!

               

              Now another question: I am trying to calculate an index that has 2011 as the reference.

              Index = sum$(Sales) / ((sum ${<Year={2011}>} total (Sales))/12) (when I am considering a month)

              if I wanted to consider my monthgroups, my index base would have to be the sum of all monthgroups in 2011 split by 10 (number of groups in the year). But if I use the same approach:

              Index = sum$(Sales) / ((sum ${<MonthGroups={M1-11, M2-11, ...}>} total (Sales))/10)

              or

              Index = sum$(Sales) / ((sum ${<MonthGroups={M1-11}+{M2-11}+ ...{M10-11}>} total (Sales))/10)

              Qklikview only considers the total of the year, and is not showing the sum of MonthGroups. Do you know how this happens and how i can solve it?

               

              Thanks

                • Group months in calendar
                  jagan mohan rao appala

                  Hi,

                   

                  Use the below expression

                   

                  Index = sum(Sales) / ((sum( total Aggr(Sum(Sales), MonthGroups))/10)

                   

                  Hope this helps you.

                   

                  regards,

                  Jagan.

                  • Group months in calendar
                    Celambarasan Adhimulam

                    Hi,

                          For month groups use expression like this =Sum(aggr(Sum(TotalSales),MonthGroups))

                     

                    Hope it helps

                    Celambarasan

                      • Group months in calendar

                        No, unfortunately it is not working, it summed all my products (ignoring my product selection), but still did not show the correct value – it was the total 2011 value and not the sum for all groups of months.

                        Qlikview is only considering distinct time periods. Do you know if there is any way to tell in the set analysis that I want all monthGroups, regardless of the month repetition in them?

                        Or as an easier example, is there any way I can tell qlikview to ignore the time period selections and consider the same month twice, like:

                        =(sum({<Year=, [Quarter/Year]=, Month={‘Jan-11’}+{‘Jan-11’}>} Total<Product>Sales

                        ?

                        When I do something like this qlikview only considers January11 once. Anyone has any idea on this? Thanks