Using functions in Set Analysis Modifier

[Anonymous]

Hi All,

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

or

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

or

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

[maxgro · Accepted solution]

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

[Oleg_Troyansky]

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])

or

= 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!