topic Trouble with Set Analysis in QlikView

Trouble with Set Analysis

Tue, 21 Apr 2009 09:21:00 GMT

Can someone tell me the difference between these two expressions because i can't work it out 😉 BUT they give me different figures (the 2nd one gives me the correct figure). What i'm trying to get is the sales for the company regardless of a selection (in this case the sales rep). The first expression seems to take into account the reps figures somehow...

=sum({1<InvoiceDate={"=inmonth(InvoiceDate,Today(),0,7)"}>}Value)

AND

=sum({<SalesRep=>} if(inmonth(InvoiceDate,Today(),0),Value))

The data structure is very simple with a sales table (InvoiceDate) and a customer table (SalesRep)

Trouble with Set Analysis

pover — Tue, 21 Apr 2009 10:20:27 GMT

The nested expression inmonth is over the filtered selection even though sum is over the whole universe. For example,

=sum({1<Year={"=max({1} Year)"}>} Sales)

will not change regardless of the selection, but

=sum({1<Year={"=max(Year)"}>} Sales)

will change when certain selections are made that filter the Year indirectly.

Now, it's just a matter of seeing how to apply the function inmonth over the whole universe of datos.

Pover

Trouble with Set Analysis

Tue, 21 Apr 2009 11:13:36 GMT

Thanks Pover for clearing that up... any ideas how to get inmonth({1}...)

Would something like this work (taking your example)??

=sum({1<Year={"=aggr({1} max(Year),SalesRep)"}>} Sales)

Trouble with Set Analysis

pover — Thu, 23 Apr 2009 00:44:24 GMT

Rob,

It should be simpler than using a aggr. Taking your example again and given you have or can create an additional field in the data cloud that looks something like the following:

month(InvoiceDate) as InvoiceMonth;

the following formula should work and be completely independent of the selections you make:

=sum({1<InvoiceMonth={$(=month(today()))}>} Value)

Tell me how it goes.

Pover