topic Re: Set analysis operator help in QlikView

Set analysis operator help

Tue, 27 Aug 2013 16:28:40 GMT

I'm trying to create an expression that will look at sales for the current fiscal year and the current month. I also need to use a bookmark in my set analysis to only includes certain products (too many to use in set analysis). My problem lies with the intersection of the current selections and the bookmark. I do want to use the current selections, but I want to explicitly define the month as the current month.

Sum({$*BM08 <[Fiscal Year]={'$(FiscalCurrentYear)'},Month={$(=Month(Today()))}}>}[Sales Amount])

With the above expression, when no month is selected or the current month is selected, it works. But, when any month other than the current month is selected, I get no records even if its in the current fiscal year. Is there a way I can explicitly define the year and month, but still include current selections and the bookmark?

Re: Set analysis operator help

Oleg_Troyansky — Tue, 27 Aug 2013 18:21:14 GMT

Try swapping the two Identifiers:

Sum({BM08 * $<[Fiscal Year]={'$(FiscalCurrentYear)'},Month={$(=Month(Today()))}}>}[Sales Amount])

The logic being: you are trying to replace Year and Month in the current selections, not in your bookmark. Keep in mind that the modifiers apply to each ID, not to both of them together. The generic syntax is:

ID1 <Modifiers> * ID2 <Modifiers>

In your case, ID1 = BM08, with no modifiers

ID2 = $ (Current selections), withe the Modifiers for year and Month.

best,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

Re: Set analysis operator help

Tue, 27 Aug 2013 19:43:20 GMT

That was indeed my issue! Thank you for the explanation along with the answer. I've not seen it explained that way and it makes much more sense now.

Thank you for your time and expertise!