Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Set Analysis SUMing Variables

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?

6 Replies
swuehl
Not applicable

Re: Set Analysis SUMing Variables

Not sure if I understand.

What's the definition of vNetPaidTravelers and vGrossPaidTravelers?

Do your variables expand to a valid field name?

Not applicable

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.

swuehl
Not applicable

Re: Set Analysis SUMing Variables

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 applicable

Re: Set Analysis SUMing Variables

Yes totally makes sense!

thanks you

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

swuehl
Not applicable

Re: Set Analysis SUMing Variables

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

Not applicable

Re: Set Analysis SUMing Variables

Nope totally clear now thanks!