5 Replies Latest reply: May 18, 2016 5:53 PM by Sunny Talwar RSS

    Set Analysis - Expressions & Variables w/ Dates

    Meg Kish

      Hi all-

       

      I've read through lots of questions and blog posts regarding how to show select/show data based on a time frame but I'm still coming up short...

       

      I would like to show my dashboard to show the most current month (which happens to be last month, April).  I've played around with the Master Calendar, Set Expressions and Variables and can't get it to calculate right...

       

      I'm trying to pull the Responsible $ by the Date of Incident and have it show the most current month (April 2016).

       

      I've tried using the Set Analysis Wizard and have tried a combination of options and can't get it to calculate.  I'm wondering if the issue lies in how the Master Calendar is set-up // connected to my data?  Any helps is appreciated, getting frustrated with my limited experience in scripting...

       

      Master Calendar

      Load

      TempDate AS [Date of Incident],

      week(TempDate) As Week,

      Year(TempDate) As Year,

      Month(TempDate) As Month,

      Day(TempDate) As Day,

      'Q' & ceil(month(TempDate) / 3) AS Quarter,

      Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

      Day(TempDate) & '-' & Month(TempDate) as DayMonth,

      WeekDay(TempDate) as WeekDay;

       

       

      LOAD

      date(mindate + IterNo()) AS TempDate

      ,maxdate // Used in InYearToDate() above, but not kept

      WHILE mindate + IterNo() <= maxdate;

       

       

      LOAD

      min(FieldValue('Date of Incident', recno()))-1 as mindate,

      max(FieldValue('Date of Incident', recno())) as maxdate

      AUTOGENERATE FieldValueCount('Date of Incident');

       

       

      LET vLYTD = Date(MakeDate(Year(Today())-1 , Month(Today()) , Day(Today())),'YYYY-MM-DD') ;

      LET vCQTD = Date(Floor(QuarterEnd(Today())) , 'YYYY-MM-DD') ;

      LET vLQTD = MakeDate( Year(QuarterEnd(Today(),-1)) , Month(QuarterEnd(Today(),-1)) -1 , day(Today())) ;

       

      Data Table

      [Rework Data]:

      LOAD

      2016 as [Rework Year],

          "DATE" as [Date of Incident],

          "Original Ticket Foundation" AS [Original Job #],

          "Rework Ticket Foundation" AS [Rework Job #],

          CUSTOMER AS [Client],

          "PROBLEM DESCRIPTION" AS [Description],

          "Corrective Action Status",

          "Resp Area" AS [Responsible Area],

          "% Resp",

          "Resp Area (adj)",

          Pieces,

          "% Qty",

          "Qty (adj)" AS [Responsible Qty],

          "$ REWORK",

          "% $",

          "$ Rework (adj)" AS [Responsible $ ]

      FROM [lib://Rework 2016 (iwco_mlkish)]

      (ooxml, embedded labels, table is [Calc Data]);