topic Re: Set analyses challenge in QlikView

Set analyses challenge

Fri, 13 Jul 2012 09:17:42 GMT

Hi,

I;m having problems with a seamingly simple set analyses.

TABLE: INVOICES. (Invoice_ID, Relation_ID, Invoice_Date, 1 as Number of Invocies)

TABLE: CUSTOMER: (Relation_ID, 1 as Number of Invoices).

TABLE: DATE (Invoice_Date, Month, Year etc).

What I want is:

Sum ( Number of Relations WHERE Number Of Invoices in selected time dimension = 1 ).

Ofcourse Number of invoices should be over the selected period in the timetable.

The formula should also work when building a graph displaying a yearmonth dimension.

(So we can see that the number of customers with just one invoice in a period, declines.)

Thanks!

jvitantonio — Fri, 13 Jul 2012 09:52:21 GMT

sum( {$<Relation_ID = {1}>} NumofRelations )

Fri, 13 Jul 2012 10:45:17 GMT

That doesn't seem right. That would select only Relation_ID = {1}. Which is only 1 relation.

I want the number of relations that only have 1 invoice in the selected period.

swuehl — Fri, 13 Jul 2012 10:51:02 GMT

Try

=count({<Relation_ID = {"=sum([Number of Invoices])=1"}>} distinct Relation_ID)

Fri, 13 Jul 2012 12:28:52 GMT

Yep! Worked. Thanks a lot!