6 Replies Latest reply: Oct 30, 2013 9:40 AM by Jon Swedberg RSS

    Set Analysis SUMing Variables

      Just wondering if anyone can give me guidance on this one. I've created several variables using SET ANALYSIS:

       

      vCurentSY which represents a the Sales Season we are in

      vMostRecentSnapshot which grabs the most recent snapshot date we have available

      vNetPaidTravelers which represents all of our Confirmed Travelers

      vGrossPaidTravelers which represents all of our Confirmed & Cancelled Travelers

       

      I basically need to create Additional variables for Cancelled Travelers I wrote the following:

       

      SUM({$<

      dimSalesOp.SalesYear={'$(vCurrentSY)'}

      ,dimSnapshotDate.SnapshotDate={'$(vMostRecentSnapshot)'}>}

      (vNetPaidTravelers-vGrossPaidTravelers) )

       

      - This returns a value of Zero

       

      SUM({$<

      dimSalesOp.SalesYear={'$(vCurrentSY)'}

      ,dimSnapshotDate.SnapshotDate={'$(vMostRecentSnapshot)'}>}

      ($(vNetPaidTravelers)-$(vGrossPaidTravelers) ))

       

      This returns a NULL value

       

      My question is: Can this be done? Can I use a variable as a FIELD and not a VALUE in SET ANALYSIS.

        • Re: Set Analysis SUMing Variables
          Stefan Wühl

          Not sure if I understand.

           

          What's the definition of vNetPaidTravelers and vGrossPaidTravelers?

          Do your variables expand to a valid field name?

            • Re: Set Analysis SUMing Variables

              vNetPaidTravelers and vGrossPaidTravelers do indeed take into account fields from our database. I can't do specifics, but I can generally tell you what they consist of:

               

              vNetPaidTravelers

              COUNT({$<

              NetAC={'>0'}

              , NetPayingTravelers={'>0'}

              , .CancelledDateEntered={'NULL'}

              , OrderStatusCode={'AC'}

              , OrderSubStatusCode={'ACAC','ACLA'}

              , IsFreePlace={'NO'}

              >}DISTINCT Traveler_Id)

               

              vGrossPaidTravelers

              COUNT({$<

              GrossAC={'>0'}

              ,GrossPayingTravelers={'>0'}

              , OrderStatusCode={'AC','CA'}

              ,IsFreePlace={'NO'}

              ,SalesYear={'$(vCurrentSY)'}

              ,SnapshotDate={'$(vMostRecentSnapshot)'}

              >}DISTINCT PaxOrder_Id)

               

              To be clear  though, I don't need a specific solution (Although I am certainly open to one) - there are other fields in our tables I can use to establish Cancelled Travelers. I have tried to use Variables in similar ways in SET ANALYSIS with little success. I always get them to work as Values:

               

              example:

              SalesYear={'$(vCurrentSY)'}

               

              But cannot get them to calculate properly as FIELDS

               

              COUNT({$<{SalesYear={'$(vCurrentSY)'}>}DISTINCT vGrossPaidTravelers)

               

              Just want to know if variables CAN be used in this manner.

                • Re: Set Analysis SUMing Variables
                  Stefan Wühl

                  I believe a variable will always hold a single value, not an array / field / dimension.

                   

                  You can set the variable to a field name, then use dollar sign expansion to replace the variable by the name and QV will 'interprete' the variable as a field.

                   

                  But your current definition is none of both, it's not a field name and doesn't even come close to an array / field / dimension, because you are using an aggregate function, which will return a single value / scalar.

                  [edit: Or, if you just defined the text in the variable, at best the variable will be replaced by the aggregation function text, but this will lead to an aggregation within an aggregation and is not allowed except using advanced aggregation (aggr() function).]

                   

                  Does this makes sense?