topic Re: Set Analysis - how to show value greater than.. in QlikView

Set Analysis - how to show value greater than..

anitamelbye — Tue, 29 Dec 2015 14:42:38 GMT

Hello all you clever People!

I'm kind of New to Qlikview, and I'm sorry for this kind of simple question.

I have a "minor" problem, and I have now spendt "numerous hours" trying to figure this out, and I need som help.

I am loading an Excel-sheet With over 200.000 lines, but I have tried to simplifyed it here in my excample 🙂

Number	Valueref	Ref	Date from	Date to	Value
10000	I001		01.01.2015	31.12.2099	100
10001	I001	10001	01.01.2015	31.12.2015	80
10001	I001	10001	01.01.2016	31.12.2099	60
10002	I001		01.01.1994	31.12.2013	100
10003	I001	10003	01.01.2015	31.12.2099	100

I would like the chart-pivot to give me follow result:

10000	100
10001	80
10002	100
10003	100

I either get nothing at all, or I get number 10001 to give me value 140.

I have read "all" the greater than-treds I have found on this forum, and I have tried maybe over 100 differens ways to write my set-sentence, but I just can not figure it out..

So please, help me, anyone? 🙂

Best regards,

"in love with QV"

Re: Set Analysis - how to show value greater than..

Gysbert_Wassenaar — Tue, 29 Dec 2015 15:27:11 GMT

sum({<Value={'>60'}>}Value)

Re: Set Analysis - how to show value greater than..

anitamelbye — Tue, 29 Dec 2015 15:30:28 GMT

Thank you for giving such a quick reply.

I may not have been very specific in my request; I will try to reformulate :

When today is dec 29. 2015, I would like the value to show 60. But NeXT year I would like the value to show 80.

Do you have any suggestion, I would be happy to try it out 🙂

Re: Set Analysis - how to show value greater than..

Gysbert_Wassenaar — Tue, 29 Dec 2015 15:38:22 GMT

Create an input box with a variable (for example vLimit) and use the variable in the expression: sum({<Value={'>$(vLimit)'}>}Value). That way users can change the value of the variable at any time and the expression will use the value the user gave the variable.

Re: Set Analysis - how to show value greater than..

swuehl — Tue, 29 Dec 2015 15:41:15 GMT

I haven't really understood how you use your from and to dates.

Shouldn't you show 80 this year and 60 next year for number 10001? And Nothing today for number 10002?

Re: Set Analysis - how to show value greater than..

anitamelbye — Tue, 29 Dec 2015 15:55:19 GMT

I will try to explain swuehl 🙂

My numbers are employee-numbers, and value indicates how much they work in %.

They all work 100% except for employee 10001, who only Works 80% this year, and will start to work 60% from next year. Was that a good explanation? 🙂

Re: Set Analysis - how to show value greater than..

anitamelbye — Tue, 29 Dec 2015 15:58:43 GMT

I tried to write following:

=sum({<[Date to]={'>$(vLimit)'}>}Value) and letting vLimit = 29.12.2015

But, I'm sorry to say, it dit not give the right answer,

Re: Set Analysis - how to show value greater than..

swuehl — Tue, 29 Dec 2015 16:07:08 GMT

I think you also need to limit Date from values:

=sum({<[Date to]= {">$(vLimit)"}, [Date from] = {"<$(vLimit)"}>} Value) and letting vLimit = 29.12.2015

And if you want to use reference dates e.g. as dimension in a chart, maybe have a look at:

Creating Reference Dates for Intervals

Re: Set Analysis - how to show value greater than..

anitamelbye — Tue, 29 Dec 2015 16:17:04 GMT

I will definitely look in to how to create Reference date for Intervals, that is excatly what I need. Thank you for giving me that link.

I changed your sentence to:

=sum({<[Date to]= {">=$(vLimit)"}, [Date from] = {"<=$(vLimit)"}>} Value) , and now it works!

THANKS A MILLLION! (Now I can sleep tonight) 🙂

Re: Set Analysis - how to show value greater than..

rohityadav5 — Fri, 02 Dec 2022 05:29:13 GMT

Miss You!!