topic How to use variable in Set Analysis in QlikView

How to use variable in Set Analysis

Tue, 08 Dec 2015 08:54:29 GMT

Hi All,

I would like to use Variable in Set Analysis:

vStartDate='2015-1-1'

vEndDate='2015-2-1'

How to use it in pivot table expression sum({<YearMonth={'>=vStartDate<vEndDate'}>}revenue) ?

Thanks.

Re: How to use variable in Set Analysis

tresB — Tue, 08 Dec 2015 08:58:08 GMT

The field you are comparing with (YearMonth) data format has to match with the variable content. That is what you have to make sure. How does your YearMonth data look like? Which function have you used in the script to create it?

Re: How to use variable in Set Analysis

MayilVahanan — Tue, 08 Dec 2015 09:05:00 GMT

Try like this

=Sum({<YearMonth= {'>=$(vStartDate)<=$(vEndDate)'}>}revenue)

Hope YearMonth filed has same format as in Variables (i.e. YYYY-M-D) If not , please mention the format for YearMonth Field

Re: How to use variable in Set Analysis

prieper — Tue, 08 Dec 2015 09:06:00 GMT

In order not to get trapped by different formats, it makes sense to convert the dates to numbers and the make a numerical comparison.

HTH Peter

Re: How to use variable in Set Analysis

Tue, 08 Dec 2015 09:07:52 GMT

Hi tresesco,

Thanks for your prompt reply, My YearMonth format is the same as the variable like 'YYYY-MM-DD'

Re: How to use variable in Set Analysis

jagan — Tue, 08 Dec 2015 09:08:42 GMT

Hi,

Try like this

sum({<YearMonth={'>=$(=vStartDate)<$(=vEndDate)'}>}revenue)

Note: YearMonth date values and variable values should be in same format.

Regards,

jagan.

Re: How to use variable in Set Analysis

mayankraoka — Tue, 08 Dec 2015 09:12:37 GMT

Hi ,

You can use this:

sum({<YearMonth={'>=$(=$(vStartDate))<$(=$(vEndDate))'}>}revenue)

Regards,

Mayank

Re: How to use variable in Set Analysis

tresB — Tue, 08 Dec 2015 09:13:26 GMT

Try like:

sum({<YearMonth={'>=$(vStartDate)<$(vEndDate)'}>}revenue)

Re: How to use variable in Set Analysis

Peter_Cammaert — Tue, 08 Dec 2015 09:49:11 GMT

The basic rule - that you can make out from tres' suggestion and all identical copies down below (?) - is that in a set modifier you specify either exact field values to use, or a "search" string (like when you start typing in a list box). A search string will not handle variables. But in a set modifier it will handle $-sign substitution to translate a variable reference into its actual content.