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

    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

        • Re: IF condition not working correctly
          Tresesco B

          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 mohan rao appala

            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
                    jagan mohan rao appala

                    Hi,

                     

                    Please find attached file for solution.

                     

                    Regards,

                    Jagan.

                      • Re: Re: IF condition not working correctly

                        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 mohan rao appala

                            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
                                    jagan mohan rao appala

                                    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.

                                      • Re: Re: IF condition not working correctly

                                        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