# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Contributor II

## Substituting a Field Value in a Set Analysis Expression

Hello,

I have a situation where I need to compute cancelled quantity based off a different calendar from the Master Calendar. Invoice reporting is based on the following Master Calendar:

Master Calendar:

• InvYear
• InvMonth
• InvoiceDate

Cancelled orders reporting is based on the following orders calendar:

Orders Calendar:

• SOYear
• SOMonth
• OrderDate

I am trying to use the following set expression to sum cancelled quantity per month based in the Orders Calendar.

=Sum({\$<SOMonth={‘Jan’}, SOYear={"Selected Invoice Year"}>} CancelledQty)

Question, is there a set modifier function that allows the use of  "Master Calendar” where, as I select a year from the Master Calendar, the value of the year (i.e. 2015) is used as the parameter for the SOYear={"Selected Invoice Year"} set modifier?

Thank you in advance for any assistance with this question.

1 Solution

Accepted Solutions
Not applicable

## Re: Substituting a Field Value in a Set Analysis Expression

Smith, Please create a variable Let vInYear = Max(InYear) & use the variable in set analysis

Sum({\$<SOMonth={‘Jan’}, SOYear={"\$(vInYear)"}>} CancelledQty)

If you not interested adding the variable try like below.

Sum({\$<SOMonth={‘Jan’}, SOYear={"\$(=Max(InYear))"}>} CancelledQty)

5 Replies
Valued Contributor III

## Re: Substituting a Field Value in a Set Analysis Expression

try using a canonical date

Canonical Date

Not applicable

## Re: Substituting a Field Value in a Set Analysis Expression

Smith, Please create a variable Let vInYear = Max(InYear) & use the variable in set analysis

Sum({\$<SOMonth={‘Jan’}, SOYear={"\$(vInYear)"}>} CancelledQty)

If you not interested adding the variable try like below.

Sum({\$<SOMonth={‘Jan’}, SOYear={"\$(=Max(InYear))"}>} CancelledQty)

MVP

## Re: Substituting a Field Value in a Set Analysis Expression

=sum({\$<SOMonth={‘Jan’}, SOYear={"\$(=max(InvYear))"}>} CancelledQty)

=sum({\$<SOMonth={‘Jan’}, SOYear={"\$(=only(InvYear))"}>} CancelledQty)

Contributor II

## Re: Substituting a Field Value in a Set Analysis Expression

Works Great!!  Thank you dathu.qv

Contributor II

## Re: Substituting a Field Value in a Set Analysis Expression

Thank you Massimo.  This option works perfectly as well.