Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andre_ficken
Partner - Creator
Partner - Creator

Set analysis using getfieldselections() result

Hi there,

I am stuck on a set analyses definition using the result of a getfieldselections result... I am not sure whether this will actually work...

This is my set analyses in simple format: Sum( {1 <  [Turnover year] = {"<2014"} >} [Turnover amount])

This works fine.

There is a general selectionbox  on a Year column... selections can be, for example, a single year (eg. 2014)  or multiple years (eg. 2014 and 2015).

I would like to sum all turnover in years less than the first year in my getfieldselections() result.

When selecting 2 years, a left(getfieldselections([Year]), 4) will give me the first year. 

So in short: How do I replace this: {"<2014"} in the set analyses by using the result of left(getfieldselections([Year]), 4)

I have been struggling with {"<$(=left(getfieldselections([Year]), 4))"} but no luck on the expected result sofar...

any help to resolve this is gladly accepted.....

1 Solution

Accepted Solutions
sunny_talwar

What if you do this:

Sum({1<[Turnover year] = {"(='<' & Min([Turnover year]))"} >} [Turnover amount])

View solution in original post

4 Replies
sunny_talwar

What if you do this:

Sum({1<[Turnover year] = {"(='<' & Min([Turnover year]))"} >} [Turnover amount])

sunny_talwar

Or this:

Sum({1<[Turnover year] = {"$(='<' & Num(Left(GetFieldSelections(Year), 4)), '##')"}>} [Turnover amount])

andre_ficken
Partner - Creator
Partner - Creator
Author

Hi Sunny, I am more in favor of the last example using the getfieldselections().

I cannot get it to work though.

My base line that works correctly is:   Sum( {$ <[Omzet jaar] = {"<2014"} > } [Omzet bedrag]))

The part that I need to change is: <[Omzet jaar] = {"<2014"} >

in the {"<2014"} part I need the year replaced by the value of left(GetFieldSelections([Jaar factuur]),4)

Omzet jaar = Revenu Year, Omzet bedrag = Revenu Amount, Jaar factuur = selection,

I am still struggling with it as you can see...

Finally after I get this to work, I would like the pivot table to only show new customers (no revenu

BEFORE getfieldselections() years, but EXISTING revenu IN getfieldselections() years. How can I keep new customers on this list and eliminate existing customers??

Thanks in advance again for your help!!

andre_ficken
Partner - Creator
Partner - Creator
Author

Hi Sunny, After a lot of trying and failure I finally managed to get the function working.

Can I ask you 1 one thing related to this??

In my pivot table I have salesrep, customer and turnover of the selected year when the customer does not have any turnover in previous years. if there is no revenue the turnover column is null (shows '-').

How can I reduce the pivot to only show me the new customers?? I have tried to re-use the formula

of the expression. Since the outcome is the same, it should just display another column. The expression is ok, but clicking ok after editing the dimension it displays the message 'Error in calculated dimension' where I expect the other column.