topic Re: using function in set analysis in QlikView

using function in set analysis

Thu, 30 Mar 2017 15:10:43 GMT

Hi all,

I'm trying to use the year function within set analysis.

Sum({<year([Purchase Creation Date]))>='($(vPrevYear)}>} [Field I want to sum])

Its not recognizing the function for some reason. I cant hook it up to the master calendar year because I need this specific year the master calendar is hooked up to a different field. Is there an easy way to do this without creating a whole separate field on the load script?

Thanks!

Re: using function in set analysis

swuehl — Thu, 30 Mar 2017 15:13:21 GMT

You can only use field names on the left of the equal sign in a set expression, no functions.

Depending on your date format (e.g. MM/DD/YYYY), you can use a search:

Sum({< [Purchase Creation Date] = {"*/$(vPrevYear)"} >} [Field I want to sum])

Re: using function in set analysis

vishsaggi — Thu, 30 Mar 2017 15:18:28 GMT

You cannot write this way in set analysis. Create a Field in your load script it would be easy.

YEAR([Purchase Creation Date]) AS PurYear,

Then use

= Sum({< PurYear = { "=$(>= $(vPrevYear))" } >} [Field I want to sum])

Re: using function in set analysis

aarkay29 — Thu, 30 Mar 2017 15:19:34 GMT

in that case you have to use something like this

Sum({<[Purchase Creation Date]={">=$(=AddYears(YearStart(Max([Date])),-1))"}>}[Field I want to sum])

Re: using function in set analysis

sunny_talwar — Thu, 30 Mar 2017 15:20:58 GMT

You can try this too...

Sum({<[Purchase Creation Date] = {"=Year(Only({1}[Purchase Creation Date])) >= $(vPrevYear)"}>} [Field I want to sum])

Re: using function in set analysis

swuehl — Thu, 30 Mar 2017 15:32:48 GMT

I missed the > in your comparison, but you should be able to use it in the search as well:

Sum({< [Purchase Creation Date] = {">='01/01/$(vPrevYear)'"} >} [Field I want to sum])

Adapt to your date format.