8 Replies Latest reply: May 24, 2016 11:47 AM by Jason Campbell RSS

    Set Analysis and Custom Table Help Needed

    Jason Campbell

      I have two issues that have effectively become a road block.  I've tried many, many different approaches and cannot arrive at a solution that works 100%.  They either do not work at all or partially.  Here are my dilemmas:

       

      1. I have three listboxes for the user to select what rows/columns they want in the straight table..  1) Dimensions 2) Metrics 3) Date Views.  Dimensions and Metrics work fine.  When I select LYTD (Last Year to Date), the YTD column appears too.  I assume it has to do with Wildmatch and/or the asterisks around LYTD. 

       

      Here is the conditional show expression I'm using for LYTD  YTD is the same, with the exception of LYTD is replaced with YTD:

      =WildMatch ( GetFieldSelections(_Metrics, '|'), '*NB*' )  and WildMatch ( GetFieldSelections(_Dates, '|'), '*LYTD*' ) = 1

       

      I've tried Match, removed the asterisks, etc...  I've also used =SubStringCount(Concat(_Columns, '|'), 'xxx')  But nothing works.

       

      Here is the inline table I'm loading:

      Dates:

      Load * Inline

      [_Dates,_DateNum

      Yr/Mth,00

      YTD,01

      LYTD,02

      SMLY,03];

       

       

      2. Set analysis and a date range.  To populate the LYTD selection above, again, I've tried many different expressions.  Here are a few:

      =Sum({$<FiscalYearMonth={">=$(vpFYMStart) <=$(vprevcurrFYM)"}>} sold)

      //sum({$<FiscalYear={'$(vpFY)'}>}sold)

      //sum(if(FiscalYearMonth>='$(vpFYMStart)' and FiscalYearMonth<='$(vprevcurrFYM)',sold))

      //Sum({$<FiscalYearMonth={">=$(vcFYMStart)-100 <=$(vcurrFYM)-100"}>} sold)

      //Sum({$<FiscalYearMonth={">=$(vpFYMStart) <=($(vcurrFYM)-100)"}>} sold)

       

      vpFYMStart = Previous Fiscal Year Month Start.  Ex. 201500.

      vprevcurrFYM = Previous (current) Fiscal Year Month.  Mirrors the current FiscalYearMonth to one year ago.

      vcurrFYM = Current Fiscal Year month.

       

      Most of them work if a date is not selected.  Once a date is selected, it zeros out.  In the set analysis, $ works as opposed to 1.  I need the user to be able to select a fiscal year and month(2) in 2016, and see the sum for the same date range in the previous year.  I feel like it is or should be simple and I'm overthinking it.  But, I could be wrong. 

       

      Any and all help will be greatly appreciated.