2 Replies Latest reply: Aug 17, 2012 5:03 AM by walkdvqv RSS

    Dynamic Month Columns

      Hi,

       

      I have a table with columns for 3 fiscal months from the maximum selected fiscal month.

      So for example if I chose just July it would show Apr, Jun & Jul data or if I selected a full financial year it would show the last 3 months (jan, feb, mar).

       

      I have 2 issues:

       

      a)  I am currently doing this using the logic of     max(FiscalMonth) for the most recent month, max(FiscalMonth)-1 for the previous month etc.

           This will however not work if I am in the first month of a fiscal year. If I select Apr I would still want to see the last 2 months of the previous fiscal year.

       

      b)  I can only get the label for each of these columns to show the fiscal month number and I want the month name. I'm sure this is quite simple but just can't figure it out.

       

       

      The data also includes calendar month and year but no date field.

       

      Can anyone help? I am still very new to Qlikview so any help would be much appreciated!

        • Re: Dynamic Month Columns
          Dmitry Gudkov

          a) It's not exactly clear from your post if FiscalMonth is only months (like Jan, Feb, Mar, etc.) or combination of month and year (e.g. Apr-2012). You need to use combination, otherwise you won't be able to cross years.

           

          Try using function AddMonths. E.g.

           

          Sum( {< FiscalMonth={' $(=AddMonths(max(FiscalMonth), -2) )'} >} Amount)

           

           

          b) Have you tried MonthName function?

            • Re: Dynamic Month Columns

              I have managed to resolve my first issue now.

               

              As regards showing the month name, monthname function does not work as I am using fiscal month. So the fiscal month field has the values 1 to 12 but 1 is April not January.

               

              There is another field in the data called Month Name.

               

              I basically want to show the Month Name field based on the fiscal month condition.