topic Re: Variable substitution in Set Analysis in QlikView

Variable substitution in Set Analysis

alexis — Wed, 25 Jun 2014 09:10:58 GMT

I have the following set analysis formula (only a part showing...

I want to replace what you see in bold below withe contents of a table column (let's call it Field1) that contains values such as 'CM','CY' etc ....

= count({<[CM] = {1}, [CCS_Subscriber.CCS_Subs

I know the answer is something of the form:

= count({<[$(=Field1)] = {1}, [CCS_Subscriber.CCS_Subs

but cannot seem to get it to work. Any ideas?

Thanx in advance

Re: Variable substitution in Set Analysis

ashfaq_haseeb — Wed, 25 Jun 2014 09:17:54 GMT

MAy be try like below

= count({<["$(=Field1)"] = {1}, [CCS_Subscriber.CCS_Subs

Regards

ASHFAQ

Re: Variable substitution in Set Analysis

Wed, 25 Jun 2014 09:20:32 GMT

May be this will help you

Re: Variable substitution in Set Analysis

alexis — Wed, 25 Jun 2014 09:26:50 GMT

Thanks Ashfaq - does not seem to work for some reason.....

Re: Variable substitution in Set Analysis

jonathandienst — Wed, 25 Jun 2014 09:28:07 GMT

Does Field1 have a single distinct value in the context for this expression? If the expression is inside table, remember that the set analysis context is global (ie you cannot have Field1 evaluated on a row by row basis).

Jonathan

Re: Variable substitution in Set Analysis

alexis — Wed, 25 Jun 2014 09:36:37 GMT

Hi Himark

Thank you for your example and the effort in trying to help me - look at the example below for a fuller explanation..

Alexis

Re: Variable substitution in Set Analysis

ashfaq_haseeb — Wed, 25 Jun 2014 09:39:34 GMT

Hi,

Can you attache sample qvw file with expected output.

Regards

ASHFAQ

Re: Variable substitution in Set Analysis

alexis — Wed, 25 Jun 2014 09:45:27 GMT

Hi Jonathan,

Here is a more detailed explanation.

My code at the moment may have

= count({<[CCS CM] = {1}, [CCS_SubscriberStatusActive] = {1}>}SubscriberKey)

Elsewhere I may have a formula of the form:

= count({<[FSB CM] = {1}, [FSB_PackageStatus] = {1}>}PackageKey)

Both would give "current month" figures - as you may have guessed I have several calendars - I have a CCS calendar and an FSB calendar - hence the flags "CCS CM" and "FSB CM"

I have other flags in my calendars such as "xxx CY" for current year, "xxx CQ" for "current Quarter" etc...

I want to give the user the option to select from a list of metrics from a drop down list that will contain:

- Current Year

- Current Quarter

- Current Month

I created a table

CurrentOptionsMap:

LOAD * INLINE [

TimeText, TimeVariable

'Current Month', 'CM'

'Current Year', 'CY'

'Current Quarter', 'CQ'

];

When the user selects an option I want it to use that metric to construct say "CCS CQ" for the current quarter where the CCS calendar is used etc ... so in the first example it will be something like:

= count({<[CCS "$(=TimeVariable)"] = {1}, [CCS_SubscriberStatusActive] = {1}>}SubscriberKey)

and in the 2nd:

= count({<[FSB "$(=TimeVariable)"] = {1}, [FSB_PackageStatus] = {1}>}PackageKey)

etc...

Hope this is clearer..

Re: Variable substitution in Set Analysis

alexis — Wed, 25 Jun 2014 09:58:04 GMT

just provided (after an edit!) a detailed explanation....

Re: Variable substitution in Set Analysis

alexis — Wed, 25 Jun 2014 11:01:42 GMT

Thank you Himank -

Your solution works .. although not ideal as I have a listbox with values such as:

CQ etc ...

(equivalent to your 'AA' and 'B B') - can you think of a way to improve it by having a description for the user to choose from ... in your example a listbox that will have:

- Description of AA

- Description of B B etc