8 Replies Latest reply: Oct 1, 2018 9:12 AM by Priya LK RSS

    Help with Expression

    Priya LK

      Hi ALL,

         

      RegionIndiaUK
      MetricWeekNOW1W2W3Avg of 3WeeksW1W2W3Avg of 3Weeks
      Sales 12324565
      Margin 33334444

       

       

      I have above data. Now for Sales Metric Average is calculated in UI but for Margin Average of 3 Weeks is calculated at DB level and I have the Field "ABC" .

       

      Qtion: How to show the  "Avg of 3Weeks" for Margin and Sales in same table

       

      Please suggest.

        • Re: Help with Expression
          raman RASTOGI

          Try Something like this

          ex-Avg({<Date={">=$(=Num(WeekStart(Max(Date),-3)))<=$(=Max(Date))"}>} Sales)

           

          ex- Avg({<Date={">=$(=Num(WeekStart(Max(Date),-3)))<=$(=Max(Date))"}>} Margin )

           

          Regards

          Raman

            • Re: Help with Expression
              Priya LK

              Hi raman,

               

              Thanks for reply but just want to know how this works in row wise average calculation for each dimension values

               

              As I said I have one field ("ABC") calculated average in DB for Margin.

               

              qtion is how to use DB level calculated and UI average expression in same table

               

              Please help me if you know how to do it.

               

              Thank you

            • Re: Help with Expression
              Priya LK

              HI ALL

               

              Any suggestions please

              • Re: Help with Expression
                shweta gupta

                Hi Priya,

                You can.create an inline table containing

                Sales

                Margin

                So that you can use them as dimension in pivot. (

                 

                If this does not work then only KPI box can help 

                 

                Note: Qlik plots value corresponding to any dimension in a column I.e in Table. Someone please correct if I have stated anything wrong.

                  • Re: Help with Expression
                    Priya LK

                    Hi Sweta,

                     

                    As said, Sales and Margin are the two Matric(Field) values which contains weekly values as shown in above pic.

                     

                    For Sales, I am calculating 'Avg of 3Weeks' as Sum(values) /3 in UI but for Margin - 'Avg of 3Weeks' is coming from a field called ABC which is already calculated in DB.

                     

                    if(Metric=Sales,Sum(Values)/3,only('ABC'))

                    or

                    if(Metric=Sales,Sum(Values)/3,

                    if(Metric=Margin,only('ABC'),0))


                    How to use the DB field ABC in the above two expressions . Is my requirement is clear or need more info

                     

                    Regards

                  • Re: Help with Expression
                    Priya LK

                    Hi Sweta,

                     

                    As said, Sales and Margin are the two Matric(Field) values which contains weekly values as shown in above pic.

                     

                    For Sales, I am calculating 'Avg of 3Weeks' as Sum(values) /3 in UI but for Margin - 'Avg of 3Weeks' is coming from a field called ABC which is already calculated in DB.

                     

                    if(Metric=Sales,Sum(Values)/3,only('ABC'))

                    or

                    if(Metric=Sales,Sum(Values)/3,

                    if(Metric=Margin,only('ABC'),0))


                    How to use the DB field ABC in the above two expressions . Is my requirement is clear or need more info

                     

                    Regards