topic Simple set analysis question in QlikView

Simple set analysis question

Anonymous — Wed, 20 Jan 2016 14:39:56 GMT

Hi Community,

In the expression below I want to replace '1-6-2016' with a variable (sum( {<Datum = (Datum*{"<$(vDate)"}) >} #CNTF) is not working ). How?

I also want to aggregate this expression below. How?

sum( {<Datum = (Datum*{"<1-6-2015"}) >} #CNTF)

Regards,

Arjan IJlenhave

Re: Simple set analysis question

sunny_talwar — Wed, 20 Jan 2016 16:48:58 GMT

Not sure I understand your syntax. What is this part doing? (Datum*{"<$(vDate)"})

Re: Simple set analysis question

Wed, 20 Jan 2016 16:52:47 GMT

Your syntax is somehow misleading. can you please explain what you want to do with your expression.

You may need like below:

sum( {<Datum = {"<1-6-2015"}>} #CNTF)

Re: Simple set analysis question

Anonymous — Wed, 20 Jan 2016 17:10:29 GMT

The syntax gives in a selection all measures of dates smaller then a variable (in the example it is 1-6-2015)

Looking for smaller dates I have to aggregate the expression.

For now the variable is working, only the agrr() is not.

I want something like this:

sum( aggr( {<Datum = {"<$(vDate)"}>} #CNTF), ObjectKey)

Re: Simple set analysis question

swuehl — Wed, 20 Jan 2016 17:38:58 GMT

You can apply set expressions only to aggregation functions, not to the AGGR() function.

Try:

sum( {<Datum = {"<$(vDate)"}>} aggr( Sum( {<Datum = {"<$(vDate)"}>} #CNTF), ObjectKey))

Re: Simple set analysis question

Peter_Cammaert — Wed, 20 Jan 2016 17:59:42 GMT

Ah, so do we again have a documentation error that has been hiding in QV Desktop help since a while?

Re: Simple set analysis question

swuehl — Wed, 20 Jan 2016 18:01:54 GMT

Yes, at least I have never made it work as defined in the HELP.

Re: Simple set analysis question

Anonymous — Wed, 20 Jan 2016 20:14:29 GMT

I hope the example below will help to solve this problem:

Expression B must result for Objectkey (123) in values #CNTF for 7-1-2015, 18-2-2015 and 15-3-2015 because these values are before EndDate 1-4-2015, Expression B must result for Objectkey (456) in values #CNTF for 7-1-2015, 18-2-2015, 15-3-2015, 15-4-2015 and 19-5-2015 because these values are before EndDate 1-6-2015.

The Final object must be without the fields (ObjectKey, EndDate en Date)

RelatieKey	ObjectKey	EndDate	Date	CNTF	B
NL#3117522	123	1-04-15	7-1-2015	-9,17	-9,17
NL#3117522	123	1-04-15	18-2-2015	-10,33	-10,33
NL#3117522	123	1-04-15	15-3-2015	-10,33	-10,33
NL#3117522	123	1-04-15	15-4-2015	114,58	0,00
NL#3117522	123	1-04-15	19-5-2015	104,17	0,00
NL#3117522	123	1-04-15	17-6-2015	93,75	0,00
NL#3117522	456	1-06-15	7-1-2015	-9,74	-9,74
NL#3117522	456	1-06-15	18-2-2015	-9,74	-9,74
NL#3117522	456	1-06-15	15-3-2015	-9,74	-9,74
NL#3117522	456	1-06-15	15-4-2015	-9,74	-9,74
NL#3117522	456	1-06-15	19-5-2015	-9,74	-9,74
NL#3117522	456	1-06-15	17-6-2015	91,85	0,00

Re: Simple set analysis question

martijn — Thu, 10 Mar 2016 14:24:54 GMT

What if you just add a line in the script:

If(Date<EndDate, CNTF, 0) as CNTFDate