topic Re: Substituting a Field Value in a Set Analysis Expression in QlikView

Substituting a Field Value in a Set Analysis Expression

asmithids — Thu, 07 May 2015 20:07:02 GMT

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.

Re: Substituting a Field Value in a Set Analysis Expression

ramoncova06 — Thu, 07 May 2015 20:13:04 GMT

try using a canonical date

Canonical Date

Re: Substituting a Field Value in a Set Analysis Expression

Thu, 07 May 2015 20:13:17 GMT

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)

Re: Substituting a Field Value in a Set Analysis Expression

maxgro — Thu, 07 May 2015 20:16:52 GMT

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

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

Re: Substituting a Field Value in a Set Analysis Expression

asmithids — Thu, 07 May 2015 20:49:44 GMT

Works Great!! Thank you dathu.qv

Re: Substituting a Field Value in a Set Analysis Expression

asmithids — Thu, 07 May 2015 20:50:56 GMT

Thank you Massimo. This option works perfectly as well.