topic Re: Set analysis with two period variables in QlikView

Set analysis with two period variables

Wed, 25 Nov 2020 16:16:04 GMT

Goodmorning all,

I want to create weborders (PostingReference) with '1' operator for Total orders based on Year and Month.

unfortunatly, the following set analysis expression doesn't work:

=(count(DISTINCT{1<Year={$(=Only(Year))},<Month={$(=Only(Month))}>}PostingReference))

What did i wrong?

Thanks regards,

Rega

Re: Set analysis with two period variables

tamilarasu — Mon, 11 Apr 2016 08:32:56 GMT

Hi Rega,

I can see one extra angle bracket in your set analysis.

=(count(DISTINCT{1<Year={$(=Only(Year))}, < Month={$(=Only(Month))}>}PostingReference))

Reomve the angle bracket and try,

=count(DISTINCT{1<Year={$(=Only(Year))},Month={$(=Only(Month))}>}PostingReference)

Re: Set analysis with two period variables

Chanty4u — Mon, 11 Apr 2016 08:40:49 GMT

remove "<" this

=(count(DISTINCT{1<Year={$(=Only(Year))},Month={$(=Only(Month))}>}PostingReference))

Re: Set analysis with two period variables

Kushal_Chawda — Mon, 11 Apr 2016 08:42:27 GMT

try this

(count(DISTINCT{1<Year={"$(=max(Year))"},Month={"$(=max(Month))"}>}PostingReference))

Re: Set analysis with two period variables

Mon, 11 Apr 2016 08:44:35 GMT

Hi Rega Sanyoto,

Try this:

=(count(DISTINCT{1<Year={"$(=Only(Year))"}, Month={"$(=Only(Month))"}>}PostingReference))

Regards,

Anjali Gupta

Re: Set analysis with two period variables

jonathandienst — Mon, 11 Apr 2016 09:03:15 GMT

As set analysis is calculated globally, the Only() function for year and month will return null() -- unless there is only on possible value for year and month in the entire data model. You may be able to use Max() instead. You need to quote the search expressions as well:

=count(DISTINCT {1<Year={"$(=Max(Year))"},<Month={"$(=Max(Month))"}>} PostingReference)

Do you want to ignore all selections? If you wish to override only the year and month values, then drop the "1":

=count(DISTINCT {<Year={"$(=Max(Year))"},<Month={"$(=Max(Month))"}>} PostingReference)