topic Re: Expression Total in QlikView

Expression Total

Wed, 30 Nov 2011 16:17:46 GMT

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

Expression Total

Wed, 30 Nov 2011 16:20:09 GMT

Re: Expression Total

Wed, 30 Nov 2011 16:21:34 GMT

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

Wed, 30 Nov 2011 16:26:03 GMT

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

Wed, 30 Nov 2011 16:27:47 GMT

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

Wed, 30 Nov 2011 16:32:03 GMT

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

Wed, 30 Nov 2011 16:35:47 GMT

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

Thu, 01 Dec 2011 09:55:06 GMT

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

Thu, 01 Dec 2011 12:13:25 GMT

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

(Blank)

Re: Expression Total

Thu, 01 Dec 2011 14:27:51 GMT

Its the Month_PurNum

Expression Total

Thu, 01 Dec 2011 14:49:22 GMT

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

Thu, 01 Dec 2011 16:14:20 GMT

It returns "-" and not 0

Re: Expression Total

Thu, 01 Dec 2011 16:23:43 GMT

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

Thu, 01 Dec 2011 17:33:39 GMT

Do you have a personal email?