topic Re: Set Analysis SUMing Variables in QlikView

Set Analysis SUMing Variables

Wed, 30 Oct 2013 13:00:09 GMT

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

swuehl — Wed, 30 Oct 2013 13:11:27 GMT

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

Wed, 30 Oct 2013 13:20:16 GMT

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

swuehl — Wed, 30 Oct 2013 13:27:46 GMT

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?

Re: Set Analysis SUMing Variables

Wed, 30 Oct 2013 13:30:19 GMT

Yes totally makes sense!

thanks you

So if the variable = a Number I SHOULD be able to SUM it in another SET ANALYSIS correct?

Re: Set Analysis SUMing Variables

swuehl — Wed, 30 Oct 2013 13:38:47 GMT

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..

Re: Set Analysis SUMing Variables

Wed, 30 Oct 2013 13:40:05 GMT

Nope totally clear now thanks!