topic Re: Re: IF condition not working correctly in QlikView

IF condition not working correctly

Fri, 14 Nov 2014 06:11:54 GMT

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.

Thanks in advance!

Neelam

Re: IF condition not working correctly

tresB — Fri, 14 Nov 2014 06:27:09 GMT

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

jagan — Fri, 14 Nov 2014 15:45:49 GMT

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

Sat, 15 Nov 2014 16:35:59 GMT

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

jagan — Mon, 17 Nov 2014 02:46:34 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Re: IF condition not working correctly

Mon, 17 Nov 2014 03:17:53 GMT

Hi Jagan,

Thanks for your efforts!

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

jagan — Mon, 17 Nov 2014 03:38:08 GMT

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

Mon, 17 Nov 2014 03:51:39 GMT

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

jagan — Mon, 17 Nov 2014 04:10:58 GMT

Hi,

In script try like this

Sales:

LOAD MonthName,

[Product Family],

[Transaction Type],

Actuals,

Num(YrMonth) as YrMonth,

YrMonth as Date

FROM

Actuals.xls

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

Concatenate

LOAD MonthName,

[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:

Load * inline [ TimePeriod

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)

)

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

Mon, 17 Nov 2014 07:22:15 GMT

Hi Jagan,

Thanks for your efforts.

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

Budget:

LOAD MonthName,

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

Load 'Returns' as [Transaction Type],

MonthName,

null() as Budget,

[Product Family],

Num(YrMonth) as YrMonth,

YrMonth as Date resident Budget;

Concatenate(Budget)

LOAD MonthName,

[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