topic Re: Difference data values for list of values in the expression in QlikView

Difference data values for list of values in the expression

Sun, 21 Dec 2014 17:44:48 GMT

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,

Re: Difference data values for list of values in the expression

Sun, 21 Dec 2014 18:00:30 GMT

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,

Re: Difference data values for list of values in the expression

prieper — Sun, 21 Dec 2014 18:12:59 GMT

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

Re: Difference data values for list of values in the expression

Sun, 21 Dec 2014 18:24:41 GMT

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,

Re: Difference data values for list of values in the expression

Anonymous — Mon, 22 Dec 2014 06:54:34 GMT

PFA.

Hope this helps.

Thanks,

Vignesh !