13 Replies Latest reply: Dec 1, 2011 12:33 PM by maibangbee

# Expression Total

Hi All

For some reason, When I use the expression total for the below IF Statement, it seems to display as true even though we are in Nov. - NUM(month(today())) is definitely greater than data in January - Please see attached for illustration.

Expression

=IF(Month_PurNum<=NUM(month(today())),SUM(JanValue_Pur),SUM(Jan_P))

• ###### Re: Expression Total

Look at the formats of the dates you are compareing.

try

IF(Month(Month_PurNum)<=Month(NUM(month(today()))),SUM(JanValue_Pur),SUM(Jan_P))

You can also try:

Sum(IF(Month(Month_PurNum)<=Month(NUM(month(today()))),(JanValue_Pur),(Jan_P)))

• ###### Re: Expression Total

Hi

I just tried and it doesn't seem to work.

Note that the Month_PurNum is the month number.

More suggestion would be appreciated

• ###### Re: Expression Total

Alright, with that info try these:   Note: Check to make sure NUM(month(today())) is returning an integer (No Decimals)

try

IF(Month_PurNum<=NUM(month(today())),SUM(JanValue_Pur),SUM(Jan_P))

You can also try:

Sum(IF(Month_PurNum<=NUM(month(today())),(JanValue_Pur),(Jan_P)))

• ###### Re: Expression Total

Thanks for the below.

NUM(month(today()))  is returning 11 (number for Nov.) and the suggestions didnt work. Hmmm,... any other please? I have been battling with this for a bit now -:(

• ###### Expression Total

Hmm, just curious, in your second screen shot, what does each chart represent? (Top one has the 4500 value, bottom the 5672 value)

You could also check your Month_PurNum to see what the format is.

Can you upload a sample doument? (Can use scrambling if the data is confidential)

• ###### Re: Expression Total

Hi There

Thanks for your replies and help.

Data is sensitive and you will need the QVD, etc.

The First Chart represents Forecast Data per month.

The Second Chart Represents the Actual Data from SAP

The Third Chart is the summary.

And my IF statement is to say: If the month of purchase is less than this month, then display Actual data (i.e Second Chart) and if the month of purchase is greater than this month, then display forecast data.

Thanks

Maxwell

• ###### Expression Total

On your last chart what is the column directly to the left of Jan? The one that displays:

6

7

4

1

3

(Blank)

• ###### Re: Expression Total

Its the Month_PurNum

• ###### Expression Total

It seems like your problem is that for the row you are having issues with has no Month_PurNum so your formula does not return the correct results.

Try this. It should now return a 0 because it does not fit into either category.

=IF(Month_PurNum<=NUM(month(today())),SUM(JanValue_Pur),

IF(Month_PurNum>NUM(month(today())),SUM(Jan_P)))

• ###### Re: Expression Total

It returns "-" and not 0

• ###### Re: Expression Total

You could try this again:

=sum(IF(Month_PurNum<=NUM(month(today())),(JanValue_Pur),

IF(Month_PurNum>NUM(month(today())),(Jan_P))))

This is begining to be to hard without being able to look at the chart.

You could also try selecting Suppress When Value is Null for the Month_PurNum dimension. That would only work if it isa null value and not a blank value

• ###### Re: Expression Total

Do you have a personal email?