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

    Set Analysis - Expressions & Variables w/ Dates

      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


      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;




      date(mindate + IterNo()) AS TempDate

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

      WHILE mindate + IterNo() <= maxdate;




      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]:


      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)",


          "% Qty",

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

          "$ REWORK",

          "% $",

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

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

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