Difference data values for list of values in the expression

[#1]

I have if condition in the expression, FctTy filed has 3 list of values like Forecast, billing, inventory and have sum(Amt).

I have Pivot table years and months are displaying horizontal and FctTy, few other fields are on verticals. And expression is

IF(FctTy='Forecast' and Invd='0' and month(Paydt)>=month(today()), SUM(Amt)

I have data from Aug 2014 to Dec 2015 so when I displaying the data billing and inventoryis displaying data from Aug 2014 to Dec 2015. But when I see the data from Forecast, the data should from current month to future as it represents Forecast data that is the reason I used month(Paydt)>=month(today()) so that my data is from current month to future.

But it is showing all the data from Aug 2014 to Dec 2015 which is not supposed to get. I am not sure whether this formula (month(Paydt)>=month(today())) works or not. Is year comparison missing in this expression.

Unfortunately I am not able to provide any kind of sample data, I am available on skype to show how data is coming.

Appreciate you kindly help.

Thanks,

[#2]

Probably I can add this one of the example.

I have table chart having Product Id, Product Cat, =Month(Date) and expression is

=IF(([Product Id]=1 and num(month(Date),'00')>=num(month(today()),'00')),SUM(Sales),0)

I have two products Apple and Samsung for apple it has to calculate Sum(Sales) and for Samsung it has to show zero, but my table chart is showing nothing.

Is something wrong in the expression if condition, can you suggest me.

I use personal edition, pls don't send updated qvw file.

Thanks,

[prieper]

Your example does not have the "Date"-field, anyhow your formula

=IF([Product Id]=1 and month(Date),SUM(Sales),0)

is wrong (at least in this context).

Please try

SUM(IF([Product Id]=1, Sales))

should result in 2572.

If you need to have also the time-dimension involve, formula might be

SUM(IF([Product Id]=1 AND MONTH(Date) = MONTH(TODAY()), Sales))

You need to ensure - that both are dates and the formats are same.

HTH Peter

[#4]

Thanks for the quick reply, My data set has Date field,I tried this formula but it works only for Dec 2014. I have updated the data set. pls have a look and I mentioned Expected output as well.

Thanks,

[Anonymous]

Hi

PFA.

Hope this helps.

Thanks,

Vignesh !