topic Re: Using functions in Set Analysis Modifier in QlikView

Using functions in Set Analysis Modifier

Anonymous — Fri, 13 Feb 2015 19:47:16 GMT

Hi All,

1. Can i you use YEAR (FieldName) in modifier on the left side of = ?

2. Can i you use YEAR (Today()) in modifier on the left side of = ?

3. Is it possible to create a set analysis like this below ?

= COUNT({$<Year(DateField) = {'2014'}>}[pina coladas])

If yes, please let me know how

Re: Using functions in Set Analysis Modifier

maxgro — Fri, 13 Feb 2015 20:08:47 GMT

in QlikView help you can find this for set analysis syntax

set_modifier ::= < field_selection {, field_selection } >

field_selection ::= field_name [ = | += | ¬–= | *= | /= ] element_set_expression

It seems you can only use fields on the left side of set modifier

Re: Using functions in Set Analysis Modifier

Oleg_Troyansky — Fri, 13 Feb 2015 20:32:01 GMT

As Massimo correctly mentioned, you can only use Field names in SA modifiers. However, you can implement the same logic using advance search conditions in your modifiers. For example:

= COUNT({$<DateField = {"=Year(DateField) = 2014"}>} [pina coladas])

= COUNT({$<DateField = {"=Year(DateField) = $(=Year(Today()))"}>} [pina coladas])

Of course, your set analysis could be much simpler and faster if you calculated a set of date-related flags in your Calendar table. For example, if you had a "Current Year Flag" that you could call _CY_Flag, you could simply use it like this:

= COUNT({$<_CY_Flag={1}>}[pina coladas])

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!