topic Re: help with a set analysis expression. in QlikView

help with a set analysis expression.

hopkinsc — Thu, 31 Oct 2013 10:02:29 GMT

I am trying to write an expression to give me..

Sum(Sales)

where

[To Group] = {'Other'}

or [To Group] is null when PCode = {'T', 'H'}

[Invoice Date] is greater than start of current year

I have the following working..

sum({<[To Group]={'Other'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>}Sales)

and

sum({<[PCode]={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>}Sales)

But i don't know how to combine them.

Can anyone help please?

Re: help with a set analysis expression.

ronaldocarrijo — Thu, 31 Oct 2013 10:33:13 GMT

Try This:

sum({<[To Group]={'Other'} + [PCode]={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>}Sales)

Re: help with a set analysis expression.

hopkinsc — Thu, 31 Oct 2013 10:58:42 GMT

Hi, thanks for your reply.

That is good but what i want is to only show the PCode T and H where the group is null.

so i want to see all the group=other and all the PCode t and h where the group is null.

Does that make sense?

Re: help with a set analysis expression.

Gysbert_Wassenaar — Thu, 31 Oct 2013 11:06:37 GMT

Try:

sum({<[To Group]={'Other'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>+<[To Group]-={'*'},[PCode]={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>}Sales)

Re: help with a set analysis expression.

jvitantonio — Thu, 31 Oct 2013 11:28:58 GMT

Hi,

I would try this:

=sum({$<PCode={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>*<[To Group]={''},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>+<[To Group]={'Other'},PCode-={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'} >}Value)

This is summing:

PCode={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>*<[To Group]={''},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}>

The field "Value"

when PCode is T or H AND [To Group] is blank ''

<[To Group]={'Other'},PCode-={'T','H'},[Invoice Date]={'>=$(=Only(YearStart(Now())))'}

[To Group] is "Other" and Pcode is not "T" or "H" (denoted as -=)

I hope this helps.

BI Experience | A place to share our Business Intelligence experiences