12 Replies Latest reply: Mar 15, 2018 2:58 AM by Amit Saini RSS

    Expression giving different Outputs

    Amit Saini

      Hi Folks,

       

      Below is my case , I'm using below same expression in 2 charts - different dimension ,bot not sure why the results are different.

       

      =if(Line_Id='116',sum(ScheduledMinutes)+15,sum(ScheduledMinutes))/60

       

      before it was sum(ScheduledMinutes)/60 , but I have to add addition 15 mints when Line_Id is 116.

       

       

      Line_Name should be 37.9 instead of 37.3.

       

      Please suggest what's wrong? Attached is sample app.

       

      Regards,

      AS

        • Re: Expression giving different Outputs
          Amit Saini

          any suggestions????

           

          Regards,

          AS

            • Re: Expression giving different Outputs
              Kaushik Solanki

              Hi,

               

              Do you want to add 15 to each row or only once?

               

              The better idea is to do this at script level.

               

              What is the expected output you are looking for.

              If it is 46.6 then try below expression.

               

              =if(Line_Id='116',sum(ScheduledMinutes+15),sum(ScheduledMinutes))/60

               

              Regards,

              Kaushik Solanki

                • Re: Expression giving different Outputs
                  Amit Saini

                  Hello,

                   

                  To each row , whenever Line_Id='116'.

                   

                  Output should be some of 12.2+13.7+12 =37.83



                  Regards,

                  AS

                    • Re: Expression giving different Outputs
                      Kaushik Solanki

                      Try this expression.

                       

                      =Sum(Aggr(if(Line_Id='116',sum(ScheduledMinutes)+15,sum(ScheduledMinutes))/60,Station_Label))

                       

                      Regards,

                      Kaushik Solanki

                        • Re: Expression giving different Outputs
                          Amit Saini

                          Hello Kaushik,

                           

                          Need one more help , now i need to display your suggested expression to show weekly chart (Should show in one Line chart values for all Week.) Like below:

                           

                          I have changed your expression by changing Station_Label to Week.

                           

                          =Sum(Aggr(if(Line_Id='116',sum(ScheduledMinutes)+15,sum(ScheduledMinutes))/60,Week))

                           

                           

                          But looks like it's not working.

                           

                          Output should be Week1= 41.9 , Week2 = 41.0 , Week3= 112.7 , Week4= 38.2

                           

                          Regards,

                          AS

                            • Re: Expression giving different Outputs
                              Kaushik Solanki

                              Hi,

                               

                              With the same expression it shows me 41.4

                               

                              Share your app again which shows 41.9

                               

                              Regards,

                              Kaushik Solanki

                              • Re: Expression giving different Outputs
                                Sunny Talwar

                                May be this

                                 

                                =Sum(Aggr(if(Line_Id='116',sum(ScheduledMinutes)+15,sum(ScheduledMinutes))/60, Station_Label, Week))


                                Capture.PNG

                                  • Re: Expression giving different Outputs
                                    Amit Saini

                                    Sunny,

                                     

                                    I'm sorry , but still need your help , looks to me more of aggr() issue.

                                     

                                    My Total value in Plant bar chart is "48.0" when I'm not selecting any value from Plant. I need the same O/P in line chart too , when I'm not selecting any Plant.

                                     

                                    I need to add "TOTAL <Facility_Id>" in expression , but not sure where should I add this

                                    Below is my expression in Line Chart:

                                     

                                    =(Sum({<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Aggr(

                                    Count(DISTINCT {<Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Day)*

                                    Count(TOTAL <Facility_Id> DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Technology-={'No Fuel BM','No SCR inj'}>}Station_Label & Line_Name)*24, Facility_Id, Week))

                                    -

                                    Sum({<Month=,Week=,Day=,Shift_Name-={},Type_Name=,Standard_Name=,Why_Name=,Type={'PD'}>}Aggr(if(Line_Id='116',sum({<Month=,Week=,Day=,Shift_Name-={},Type_Name=,Standard_Name=,Why_Name=,Type={'PD'}>}ScheduledMinutes)+15,sum({<Month=,Week=,Day=,Shift_Name-={},Type_Name=,Standard_Name=,Why_Name=,Type={'PD'}>}ScheduledMinutes))/60,Station_Label,Week)))

                                    /

                                    Count( {$<Month=,Week=,Day=,Shift_Name-={},Quantity={"=Sum({<Month=,Week=,Day=>}Quantity) <> 0"},Type_Name = {'Downtime'}>} Quantity)

                                     

                                    Note: When I'm selecting individual Plants , Total value in bar chart is matching with Line chart. See below:

                                     

                                    Please help!

                                     

                                    Attached is sample.

                                     

                                    Regards,

                                    AS

                                      • Re: Expression giving different Outputs
                                        Amit Saini

                                        Any Suggestions Sunny , Kaushik ???

                                         

                                        Regards,

                                        AS

                                          • Re: Expression giving different Outputs
                                            Kaushik Solanki

                                            Try including plant in aggr function.

                                             

                                            =(Sum({<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Aggr(

                                            Count(DISTINCT {<Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Day)*

                                            Count(TOTAL <Facility_Id> DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Technology-={'No Fuel BM','No SCR inj'}>}Station_Label & Line_Name)*24, Facility_Id, Week,Plant))

                                            -

                                            Sum({<Month=,Week=,Day=,Shift_Name-={},Type_Name=,Standard_Name=,Why_Name=,Type={'PD'}>}Aggr(if(Line_Id='116',sum({<Month=,Week=,Day=,Shift_Name-={},Type_Name=,Standard_Name=,Why_Name=,Type={'PD'}>}ScheduledMinutes)+15,sum({<Month=,Week=,Day=,Shift_Name-={},Type_Name=,Standard_Name=,Why_Name=,Type={'PD'}>}ScheduledMinutes))/60,Station_Label,Week,Plant)))

                                            /

                                            Count( {$<Month=,Week=,Day=,Shift_Name-={},Quantity={"=Sum({<Month=,Week=,Day=>}Quantity) <> 0"},Type_Name = {'Downtime'}>} Quantity)

                                              • Re: Expression giving different Outputs
                                                Amit Saini

                                                Result is matching now by using below exp:

                                                sum({<Month=,Week=,Day=,Date>}

                                                Aggr(

                                                Count(distinct {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Day)*

                                                Count(TOTAL <Facility_Id>  DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Technology-={'No Fuel BM','No SCR inj'}>}Station_Label & Line_Name)*24, Week )

                                                )

                                                 

                                                Thanks you so much Sunny and Kaushik for all your help!

                                                 

                                                Regards,

                                                AS

                              • Re: Expression giving different Outputs
                                Krishna Nagulapally

                                Probably you might have missed to aggregate the data,especially while Line =116.