Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
Not sure if I understand.
What's the definition of vNetPaidTravelers and vGrossPaidTravelers?
Do your variables expand to a valid field name?
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.
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?
Yes totally makes sense!
thanks you
So if the variable = a Number I SHOULD be able to SUM it in another SET ANALYSIS correct?
If your variables expand to numbers and you try to use something like
=sum( $(vVar1) - $(vVar2) )
this should return the difference of the two variables, but it would be sufficient to just use
=vVar1 - vVar2
then.
Set analysis added to above sum() function won't work, because your expanded variables are not connected to your data model.
But maybe I am misunderstanding what you are trying to achieve..
Nope totally clear now thanks!