14 Replies Latest reply: Apr 29, 2009 10:06 PM by Karol Dorniak RSS

    Set Analysis - dynamic chart over the last 12 months

      Hello,

      I need some help in Set Analysis. I searched in the forum for a solution of my problem but nothing works for me.

      Problem description:

      Fields: 1) Ticketnumber (ex. 5, 14, 1265, 3459......)

      2) YearMonth (ex. 2006-Jan, 2006-Nov, 2007-Jul, 2009-Mrz ....) ==> format is 'YYYY-MMM'

      Endresult:

      I select only 1 value in the listbox "YearMonth" for example "2009-Jan" I would like to have a bar chart which shows me 12 bars, from "2008-Feb" to "2009-Jan", for each YearMonth one bar. As dimension in the barchart I have "YearMonth" and as Formula I would like to have a count(Ticketnumber).

      The selection should be flexible I always select only 1 value in the field "YearMonth":

      Selection: "2008-Sep" ==> so the bar chart includes the date range 2007-Okt to 2008 Sep

      I hope the information is enough that somebody could help me with a set analysis formula! Thanks.

        • Set Analysis - dynamic chart over the last 12 months
          Anatoly Pyatygo

          Hi, Herbert.

          What about macros?

          You can write a macro, that would have selected the correct 12 value in the YearMonth.

          For example: Selection: "2008-Sep" ==> so the 'YearMonth' Field will be includes the date range 2007-Okt to 2008 Sep.

          What about this solution?

          Best regards, Anatoly

           

          • Set Analysis - dynamic chart over the last 12 months
            Karol Dorniak

            Hi,

            Example:

            1. create new QVW file and copy this script to Edit Script:

            tickets:
            load * inline [
            Date, NoOfTickets

            2006-Jan, 10
            2006-Feb, 30
            2006-Mar, 34
            2006-Apr, 40
            2006-May, 70
            2006-Jun, 12
            2006-Jul, 33
            2006-Aug, 20
            2006-Sep, 35
            2006-Oct, 40
            2006-Nov, 60
            2006-Dec, 43

            2007-Jan, 20
            2007-Feb, 30
            2007-Mar, 40
            2007-Apr, 40
            2007-May, 43
            2007-Jun, 54
            2007-Jul, 23
            2007-Aug, 33
            2007-Sep, 55
            2007-Oct, 66
            2007-Nov, 43
            2007-Dec, 39

            2008-Jan, 74
            2008-Feb, 82
            2008-Mar, 65
            2008-Apr, 54
            2008-May, 43
            2008-Jun, 32
            2008-Jul, 34
            2008-Aug, 56
            2008-Sep, 72
            2008-Oct, 37
            2008-Nov, 52
            2008-Dec, 24

            2009-Jan, 50
            2009-Feb, 45
            2009-Mar, 44

            ];

            //i am creating a proper date field
            left join
            load
            Date,
            date(date#(Date,'YYYY-MMM'),'MMM-YYYY') as Date2
            resident tickets;

            2. Reload and Save it

            3. Create a Listbox for field Date 2. In the properties check: "Alwasys one selected value".

            4. Create Bar chart -

            a. dimension: Date2

            b. expression:

            Sum

             



            ({1<Date2={"$(='<='&Only(Date2)&'>'&Only(date(addmonths(Date2,-12),'MMM-YYYY')))"}> * $<Date2=>}NoOfTickets)

            c. sort it by Date 2 asc.

            Enjoy:) If you let me know how can I attached QVW to this post, I can give you the example.