Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF condition not working correctly

Hi Everyone,

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

Qlikview.png

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

9 Replies
tresesco
MVP
MVP

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?

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

Hi Jagan,

It didn't work

I have attached a sample QVW for the same issue.

Thanks for the help.

Neelam

jagan
Luminary Alumni
Luminary Alumni

Hi,

Please find attached file for solution.

Regards,

Jagan.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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)

)

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.

Not applicable
Author

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