topic Using set analysis without selection in QlikView

Using set analysis without selection

jjking58 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I've been trying to resolve this all day. I've looked at the boards, but was unable to find the answer i needed.

Hopefully someone can help me.

In the expression below I would like to always to show the current month (fMonth) regardless whether it's selected or not

I'm using a fiscal calendar so i'm not sure if that makes a difference.

aggr(Sum({<fYear={$(=Only(fYear))},fMonth={$(=max(fMonth))},Salesman=$::Salesman>}PartsAmt)+

sum({<fYear={$(=Only(fYear))},fMonth={$(=max(fMonth))},Salesman=$::Salesman>}EqupAmt) +

sum({<fYear={$(=Only(fYear))},fMonth={$(=max(fMonth))},Salesman=$::Salesman>}LaborAmt),Day) / COUNT(DISTINCT(InvoiceNumber))

Thank you in advance

Re: Using set analysis without selection

Frank_Hartmann — Tue, 27 Jun 2017 19:36:14 GMT

try that:

aggr(Sum({<fYear={$(=Only(fYear))},fMonth= {$(=Month(Today()))},Salesman=$::Salesman>}PartsAmt)+

sum({<fYear={$(=Only(fYear))},fMonth= {$(=Month(Today()))},Salesman=$::Salesman>}EqupAmt) +

sum({<fYear={$(=Only(fYear))},fMonth= {$(=Month(Today()))},Salesman=$::Salesman>}LaborAmt),Day) / COUNT(DISTINCT(InvoiceNumber))

Re: Using set analysis without selection

swuehl — Tue, 27 Jun 2017 19:36:38 GMT

First possible issue: Only(fYear) will only return a value if there is only one year possible. If nothing is selected, are there more than one year possible? If yes and to be on the safe side in any case, use Max(fYear) also for fYear modifier.

Re: Using set analysis without selection

swuehl — Tue, 27 Jun 2017 19:39:52 GMT

Second possible issue (like Frank also noticed): The max possible fMonth value might not be available in the max fYear.

Like for this year, max month could be June, while December might be a possible value for last year.

It's better to use set modifier on the needed granularity, i.e. Month-Year field, and clear selections in all other fields that might interfere (or use set identifier 1).

Re: Using set analysis without selection

swuehl — Tue, 27 Jun 2017 19:40:43 GMT

Re: Using set analysis without selection

jjking58 — Tue, 27 Jun 2017 19:46:55 GMT

I tried Franks suggesting and now i'm not showing any data. The original code worked as long as I selected a month from the list under fMonth. How can I force the month without selecting it is this possible?

Re: Using set analysis without selection

Frank_Hartmann — Wed, 28 Jun 2017 07:35:44 GMT

how does the values of your fmonth field look like?

can you upload your qvw (scrambled)?

Re: Using set analysis without selection

Frank_Hartmann — Wed, 28 Jun 2017 08:07:57 GMT

attached sample might be helpful:

=Sum({1}aggr(Sum({1<fyear={'$(=Year(Today()))'},fmonth={'$(=Month(Today()))'}>}PartsAmt) +

Sum({1<fyear={'$(=Year(Today()))'},fmonth={'$(=Month(Today()))'}>}EqupAmt) +

Sum({1<fyear={'$(=Year(Today()))'},fmonth={'$(=Month(Today()))'}>}LaborAmt) ,Day))/

count({1}DISTINCT(InvoiceNumber))