help with a set analysis expression.

[hopkinsc]

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?

[ronaldocarrijo]

Try This:

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

[hopkinsc]

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?

[Gysbert_Wassenaar]

Try:

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

[jvitantonio]

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 ''

OR

<[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.

JV

BI Experience | A place to share our Business Intelligence experiences