2 Replies Latest reply: Aug 27, 2013 3:09 AM by Frans Andersen RSS

    Dual set-analyses loses dimension in piviot table

      Hi all!

       

      I have a piviot table with a dimension "ID" and a set-analyses as expression:

      =Count({$<DateTime-={">$(=Min({<Station_ID={44}>}Arrival,2))"}>}DateTime)

       

      The set expression is doing the following:

      1. Picking the secound eariest date of Arrival when Station_ID is 44 for the ID in the dimension

      2. Excluding all dates for the given ID in DateTime which is after this date

      3. Counting how many rows there are left in DateTime

       

      The set-analyses gives out 0 for all ID's.

      IDSet analyses
      32-3380
      32-3390
      32-3400
      32-3410

       

      But when I select a ID it gives out the right answear

       

      IDSet analyses
      32-339320

       

      It seams that the secound set-analyses >$(=Min({<Station_ID={44}>}Arrival,2)) cant read the ID for the actual row.

       

      Is it any way to call for the value in the ID column? Something like:

      =Count({$<DateTime-={">$(=Min({<Station_ID={44}, ID={get value from first column}>}Arrival,2))"}>}DateTime)

       

      Or is it possible to solve it in anouther way? I have tried to use aggr but it gives out the same answear.

      =Count({$<DateTime-={">$(=(aggr(Min({<Station_ID={44}>}Arrival,2),ID)))"}>}DateTime)

        • Re: Dual set-analyses loses dimension in piviot table
          Stefan Wühl

          If you are using a dollar sign expansion with an expression like

           

          $(=Min(Arrival))

           

          then this will be evaluated first, in global context, not considering any dimensions of your chart (it's like a macro expansion, it will be evaluated before anything else get's done).

           

          Also your set expression is only evaluated once per chart, not considering your dimension

          (see this document for more details).

           

          So set analysis alone will not work here, and you probably need to use some other technique, like advanced aggregation.

           

          But it would be good if you could post some more details, best by posting a small sample app that demonstrate your issue.

            • Re: Re: Dual set-analyses loses dimension in piviot table

              Thanks for youre reply @swuehl!

               

              You are right that Set-analysis would not work for dual set-analyses, however I have found that it does work for a single set-analyses. When i only use one the expression it will consider my dimension. (see column 2 in Test table in attached file)

               

              Since I cant use dual set analyses for this purpose, could you please elaborate a bit on what you mean by advanced aggregation?

              Do you mean aggregating it in the load script?

              I have no experience of that, could you give me an example to lead me in the right direction? or eaven better write it in the qv-file?

               

              Thank you for taking time for my issue!