topic Using fields in Set Analysis modifiers in QlikView

Using fields in Set Analysis modifiers

Fri, 13 Feb 2015 16:44:41 GMT

Hi,

Just fresh off a Set Analysis class, I was asked for the sum of all forecasted sales since the Companystartdate.

I entered:

=sum({$<[OppStartDate]={">=Companystartdate"}>} FORAMT)

This returned uniform zeroes.

I changed it to:

= sum(if(OppStartDate>=Companystartdate,FORAMT,0)

This worked perfectly.

Can I use a field name like CompanyStartDate in the SA analysis above? do I need $-expansion here?

Don't have time to practice right now, so if anyone has a quick answer, I'd appreciate it.

Re: Using fields in Set Analysis modifiers

alexandros17 — Fri, 13 Feb 2015 16:46:48 GMT

The right side of set analysis can include field names but must represent a unique value that is you could use

Max(Companystartdate) or Only(Companystartdate) but in set analysis you cannot compare directly 2 fields ...

hope this helps

Re: Using fields in Set Analysis modifiers

Fri, 13 Feb 2015 16:52:31 GMT

well, neither max nor only worked, but at least now I know what the issue is.

thanks for the quick response! I have it working with sum(if(.. so I'll just leave it at that, and come back to this another time.

Re: Using fields in Set Analysis modifiers

Oleg_Troyansky — Fri, 13 Feb 2015 16:52:56 GMT

The difference between the IF and the Set Analysis is that the IF statement is being performed for each row of data, while the Set Analysis condition is performed once per chart. To be precise, your modifier

[OppStartDate]={">=Companystartdate"}

is working as an implied AGGR by the field [OppStartDate]. So, the comparison should be valid for each distinct value of OppStartDate. If I were to guess, I'd try something like this:

OppoertunityID={"=[OppStartDate]>=Companystartdate"}

This condition will select those Opportunities where the field OppStartDate is greater then the Companystartdate.

Another possible solution would be to use an aggregation function inside a dollar-sign expansion:

OppoertunityID={"=[OppStartDate]>=$(=min(Companystartdate))"}

Lastly, check the date formats for the two fields. Set Analysis is a bit picky when it comes to comparing date fields - the formats have to be exactly the same.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

Re: Using fields in Set Analysis modifiers

Fri, 13 Feb 2015 16:58:57 GMT

Thanks, Oleg. that gives me something to work on when I come back to this. One further quick question:

do you mean create OppID as a variable, or as a new field (e.g.

Load

if(OppStartDate>=CompStartDate,1,0) As OppID,

(select opp table)

And then build my SA statement?

Re: Using fields in Set Analysis modifiers

Fri, 13 Feb 2015 17:15:15 GMT

If you have multiple potential values within CompStartDate then yes I would create that field, from there the set analysis if very simple and spans across all companies in your data.

If you only have a single CompStartDate then using the only(CompStartDate ) option should work for you.

Something like:

=sum({$<[OppStartDate]={">=$(=Only(Companystartdate))"}>} FORAMT)

hope that helps

Joe

Re: Using fields in Set Analysis modifiers

Oleg_Troyansky — Fri, 13 Feb 2015 17:38:01 GMT

Kevin,

In my example, I made up a field that identifies the Opportunity, assuming that you have something like that.

Now, you bring up even a better point. If you calculate the condition in the script and save the result in a flag field:

Load

if(OppStartDate>=CompStartDate,1,0) As _OppDate_Flag,

(select opp table)

Then you can simplify your Set Analysis by a lot:

_OppDate_Flag = {1}

Or, you can simply multiply your measure by the same flag, without using set analysis

Re: Using fields in Set Analysis modifiers

Fri, 13 Feb 2015 18:05:33 GMT

Yep, many ways to skin a cat. I wanted to use my new toy, but as has been pointed out, since there’s a different Company start date for each company, SA may not be the best choice.

But I appreciate all the answers – gives me a better understanding!

cheers,

Kevin