9 Replies Latest reply: Nov 17, 2014 2:22 AM by neelam singh

# IF condition not working correctly

Hi Everyone,

I have a Pivot table BG looking like below showing JUN'14 data:

When I put the below condition in BG:

if(TimePeriod='MTD',Sum({<[Transaction Type]=>}[Gross Budget]),

Sum ({<YrMonth = {">=\$(=yearstart(Date(VDateMax),0,1)) <=\$(=Date(VDateMax))"},MonthName=,[Transaction Type]=>}[Gross Budget]))

Transaction Type is only applicable for Actuals, so I had to Ignore it in Budget expression above through Set Analysis.

It gives me an extra product METACAM CATTLE which is 1090000 for MTD which is actually for MAY and not present for Jun, however YTD is correct since no values for this product in earlier months.

I have kept 2nd chart MTD for reference which has 2 normal expressions for MTD and YTD and is showing correct data for the same product.

I hope you guys will be able to understand the above explaination. If not please let me know, I will try to further detail it.

Neelam

• ###### Re: IF condition not working correctly

Hi,

Said that, you have explained it nicely, expression seems good. Could you reduce your app and create a sample qvw(removing other conf info) and share here so that we can have a look and might find a reason which is not visible here in screen shot?

• ###### Re: IF condition not working correctly

Hi Neelam,

Try this expression

if(TimePeriod='MTD',

Sum({<Date={'\$(=Date(Max(Date)))'}, [Transaction Type]=>}Budget),

Sum({<Date={'\$(=Date(Max(Date)))'}, YrMonth = {">=\$(=(Var1)) <=\$(=(Var2))"},MonthName=,[Transaction Type]=>} Budget)

)

Regards,

Jagan.

• ###### Re: IF condition not working correctly

Hi Jagan,

It didn't work

I have attached a sample QVW for the same issue.

Thanks for the help.

Neelam

• ###### Re: Re: IF condition not working correctly

Hi,

Please find attached file for solution.

Regards,

Jagan.

• ###### Re: Re: IF condition not working correctly

Hi Jagan,

I saw you had put Date={'\$(=Date(Max(Date)))'} into YTD formula also and it was giving monthly values due to this.

When I removed this from YTD to get YTD values it again started giving the value 1090 in MTD for JUNE for METACAM CATTLE which should not come.

Regards,

Neelam

• ###### Re: Re: IF condition not working correctly

Hi Neelam,

The Transaction Type filter causing the issue, if you remove the selections and use below expression it works as expected

if(TimePeriod='MTD',

Sum({<Date={'\$(=Date(Max(Date)))'}, [Transaction Type]=>}Budget),

Sum({<YrMonth = {">=\$(=(Var1)) <=\$(=(Var2))"},MonthName=, [Transaction Type]=>} Budget)

)

I think it is better to remove that filter, since you are not using it.

Regards,

jagan.

• ###### Re: Re: IF condition not working correctly

Hi jagan,

I understand that filter is causing the issue. Actually this filter is not applicable to Budget, but important for Actuals. And Budget & Actuals are present together in the same chart, so it needs to be ignored in Budget.

Without Transaction Type Selection, everything is coming correctly.

Hence I was trying to get it done this way.

Thanks & Regards,

Neelam

• ###### Re: Re: IF condition not working correctly

Hi,

In script try like this

Sales:

[Product Family],

[Transaction Type],

Actuals,

Num(YrMonth) as YrMonth,

YrMonth as Date

FROM

Actuals.xls

(biff, embedded labels, table is [Sheet1\$]);

Concatenate

[Product Family],

Budget,

Null() AS  [Transaction Type],

Num(YrMonth) as YrMonth,

YrMonth as Date

FROM

Budget.xls

(biff, embedded labels, table is [Sheet1\$]);

TimePeriod_TH:

MTD

YTD

];

Now use this expression in chart

if(TimePeriod='MTD',

Sum({<Date={'\$(=Date(Max(Date)))'}, [Transaction Type]-={'*'}>}Budget),

Sum({<YrMonth = {">=\$(=(Var1)) <=\$(=(Var2))"},MonthName=,[Transaction Type]-={'*'}>} Budget)

)

Or

if(TimePeriod='MTD',

Sum({<Date={'\$(=Date(Max(Date)))'}, [Transaction Type]=>}Budget),

Sum({<YrMonth = {">=\$(=(Var1)) <=\$(=(Var2))"},MonthName=,[Transaction Type]=>} Budget)

)

Hope this helps you.

Regards,

jagan.

• ###### Re: Re: IF condition not working correctly

Hi Jagan,

Your suggestion didn't work, but I started from it & was able to get to the solution. What I did is:

Budget:

[Product Family],

Budget,

'Normal' as [Transaction Type],

Num(YrMonth) as YrMonth,

YrMonth as Date

FROM Budget.xls (biff, embedded labels, table is [Sheet1\$]);

Concatenate(Budget)

MonthName,

null() as Budget,

[Product Family],

Num(YrMonth) as YrMonth,

YrMonth as Date resident Budget;

Concatenate(Budget)

[Product Family],

[Transaction Type],

Actuals,

Num(YrMonth) as YrMonth,

YrMonth as Date

FROM Actuals.xls (biff, embedded labels, table is [Sheet1\$]);

Concatenating all the Transaction Types with Budget as Null helped.

Also, the expression would be now:

if(TimePeriod='MTD',

Sum({<[Transaction Type]={'Normal'}>}Budget),

Sum({<YrMonth = {">=\$(=(Var1)) <=\$(=(Var2))"},MonthName=,[Transaction Type]={'Normal'}>} Budget)

)

Thanks a lot for this idea!

Regards,

Neelam