3 Replies Latest reply: Mar 9, 2011 6:32 AM by Nicolas ALLANO RSS

    Link between table through Set Analysis


      I am dealing with support incident; each incident has 2 dates information (OpenMonth & ResolvedMonth)

      I have created 2 tables to ease the calculation of some statistics (such as YTD, YTD-1, M and M-1)

      Script to create the 1st table for the OpenMonth variable is: http://community.qlik.com/forums/t/41759.aspx

      Then to create the 2nd one, I simply do:

      "As of OpenMonth" as "As of ResolvedMonth"
      ,"OpenMonth Range" as "ResolvedMonth Range"
      ,"OpenMonth" as "ResolvedMonth"
      RESIDENT OpenAsOf;

      On top of that, I have created a slider, with following data:

      Load MonthName(Today(),-IterNo()+1) as SliderMonth AutoGenerate(10)
      while IterNo() < 10

      So in my different graph, wether I deal with OpenMonth or ResolvedMonth, I use a SetAnalysis as following:

      =count({$<[As of ResolvedMonth]=SliderMonth>} SD)

      or =count({$ <OpenMonth = {"=NUM(OpenMonth) <= NUM(SliderMonth)"}>}SD)

      Now, I want to create a summary table which look like this:

      error loading image

      Because, for tickets submitted, I need to deal with OpenMonth, and for tickets closed, I need to deal with ResolvedMonth,

      I don't think I can use either "OpenedMonth Range" or "ResolvedMonth Range" as the dimension,

      So I have created another table (which only contains the list of statistics YTD, YTD-1, M, M-1), and use "Range" as a dimension

      Load Distinct "OpenMonth Range" as Range
      RESIDENT OpenAsOf;

      Now I can't figure out how to make the "link" between the dimension "Range"and the different expression.

      For the moment, I do have

      for tickets submitted: count( { $ < [As of OpenMonth] = SliderMonth>} [SD]), but where do I mention that [OpenedMonth Range] should be equal to [Range]?

      Thank in advance,