topic Set Analysis Ignoring selections with default in QlikView

Set Analysis Ignoring selections with default

dana — Sat, 16 Nov 2024 19:47:51 GMT

Hi,

I have an expression with a default Open orders period, and ignoring Invoice dates selections.

sum({<InvoiceYear=, InvoiceQuarter=, InvoiceMonth=, InvoiceWeek=, InvoiceDate=,OpenYear=, OpenQuarter=, OpenMonth=, OpenWeek=, OpenDate=, OpenDateKey={">=$(=Num(YearStart(Max(OpenDateKey)))) <=$(=Max(OpenDateKey))"}>}Amount)

The issue is: selection of Invoice year is not ignored when Open period is selected by a user.
Can someone please explain why?

Thanks!

Re: Set Analysis Ignoring selections with default

Arthur_Fong — Tue, 29 Oct 2019 06:09:58 GMT

Can you provide a sample app that shows what is the current result shown in the expression vs the expected result that should be displayed?

Re: Set Analysis Ignoring selections with default

dana — Tue, 29 Oct 2019 07:05:31 GMT

Hi Arthur,

I Attached a sample model and screen captures.

I also updated the issue: selection of Invoice year is not ignored when Open period is selected by a user.

Thanks!

Re: Set Analysis Ignoring selections with default

Arthur_Fong — Tue, 29 Oct 2019 07:24:32 GMT

Try this:

sum({<InvoiceIssueYear=, OpenYear=, OpenDateKey={">=$(=Num(YearStart(Max({<InvoiceIssueYear=>}OpenDateKey)))) <=$(=Max({<InvoiceIssueYear=>}OpenDateKey))"}>} Amount)

Re: Set Analysis Ignoring selections with default

dana — Tue, 29 Oct 2019 07:57:41 GMT

Indeed, that works!

Frankly, I don't understand what you added, nor why it doesn't work without it..

What are the cases when dates selections are not ignored?

Thanks!

Re: Set Analysis Ignoring selections with default

Arthur_Fong — Tue, 29 Oct 2019 08:01:02 GMT

Max({<InvoiceIssueYear=>}OpenDateKey)

this will ignore the InvoiceIssueYear selection.

The expression you did earlier is max(openDateKey).

So it will still evaluate without ignoring the InvoiceIssueYear selection.

Re: Set Analysis Ignoring selections with default

dana — Tue, 29 Oct 2019 08:29:07 GMT

Hi Arthur,

Would you kindly clarify why InvoiceIssueYear= doesn't have the same impact?

Thank you!

Re: Set Analysis Ignoring selections with default

Arthur_Fong — Tue, 29 Oct 2019 08:43:13 GMT

sum({<InvoiceIssueYear=, OpenYear=, OpenDateKey={">=$(=Num(YearStart(Max({<InvoiceIssueYear=>}OpenDateKey)))) <=$(=Max({<InvoiceIssueYear=>}OpenDateKey))"}>} Amount)

This expression consists of 3 component to be evaluated:

sum(Amount)<<<This part you did well in ignoring those fields by InvoiceIssueYear=, OpenYear=.
OpenDateKey={">=$(=Num(YearStart(Max(OpenDateKey)))) <<<this part u did not insert the field to be ignored
<=$(=Max(OpenDateKey)) <<< same as this

For the expression to work well, you have to add in "InvoiceIssueYear=" to the max(OpenDateKey), so that:

- when InvoiceYear is selected, it will ignore this selection and return the max(OpenDateKey)[eg:1/Oct/2019] to the base expression of:

sum({<InvoiceIssueYear=, OpenYear=, OpenDateKey={">=[1/Jan/2019]<=[1/Oct/2019]"}Amount)