13 Replies Latest reply: Jul 31, 2018 8:46 AM by Sunny Talwar RSS

    Using expression in other expression to calculate

    Deepak Singh

      I Have Calculated MTD_VOL using this expression:


      sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

      PACK_VOL)


      and now i have to use above MTD_VOL expression in another expression for range ">0<40" i.e MTD_VOL={">0<40"} in below expression:

       

      count ( {<(MTD_VOL)={">0<40"},KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)


      How i can Achieve Please help

        • Re: Using expression in other expression to calculate
          Petter Skjolden

          You are not allowed to do nested aggregations directly in Qlik. However you can use either the Aggr() function or an "Advanced Search" to achieve it.

           

          Count ( {<UniqueDIM={

          "=

          Sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

          PACK_VOL)

          >0 AND

          Sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

          PACK_VOL)

          <40"

          },KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)

           

           

          You could put the repeated Sum-calculation in a variable as a string called vSumPVOL and make the expression simpler like this:

           

          vSumPVOL:

          Sum({<INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>} PACK_VOL)



          Then the count expression can use this variable like this:


          Count {<UniqueDIM={"=$(=vSumPVOL)>0 AND $(=vSumPVOL)<40)"}>} , KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)


          The key for this to work is that the UniqueDIM is a correctly chosen dimension for the advanced search to be matched with. To be absolutely sure that it performs the search a unique dimension could be used.


          • Re: Using expression in other expression to calculate
            Sunny Talwar

            Is there a single or multiple dimension/s against which you are trying to evaluate this condition?

            sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

            PACK_VOL) > 0

             

            and

             

            sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

            PACK_VOL) < 40?

              • Re: Using expression in other expression to calculate
                Deepak Singh

                multiple dimension

                i have to count customer for which MTD_Vol is >0<40.

                and MTD_VOL logic is =

                sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

                PACK_VOL)


                so now the actual logic will be


                count(customer) for whom MTD_VOL range will be >0<40 and KEYACCGRP2_CD={'VISI'}

                  • Re: Using expression in other expression to calculate
                    Sunny Talwar

                    May be you use need to use Aggr() in that case

                     

                    Count({<KEYACCGRP2_CD = {'VISI'}>} Aggr(If(

                     

                    Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DD-MM-YYYY'))<=$(=Date(Max(SO_DT), 'DD-MM-YYYY'))"}>} PACK_VOL) > 0

                     

                    and

                     

                    Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DD-MM-YYYY'))<=$(=Date(Max(SO_DT), 'DD-MM-YYYY'))"}>} PACK_VOL) < 40,

                     

                    Customer), ListOfDimensions))

                      • Re: Using expression in other expression to calculate
                        Deepak Singh

                        Hi Sunny,

                        I am using this in pivot table in QlikSense for hierarchy thats why I told you for multiple dimension, but in logic no need to mention dimension, I tried below without dimension but its showing null records. Please guide

                         

                        Count(

                        {<KEYACCGRP2_CD = {'VISI'}>} Aggr(If(

                         

                        Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),

                        'DD-MM-YYYY'))"}>} PACK_VOL) > 0

                        and

                        Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),

                        'DD-MM-YYYY'))"}>} PACK_VOL) < 40,

                        CUST_CD&DIST_CD)))))

                          • Re: Using expression in other expression to calculate
                            Sunny Talwar

                            I tried below without dimension but its showing null records.

                            Why would you do that? Are you not sure at what dimension level you want to evaluate this at? Let's look at an example may be that will help

                             

                            Country, Car, Sales

                            US, Civic, 10

                            US, Accord, 500

                            Japan, Civic, 500

                            Japan, Accord, 100

                             

                            Now I can see that I want Sum(Sales) to be greater than 160 and less than 590... but at what level? for Country? For Car or for Country and Car

                             

                            For Country, my output will be

                            Country, Car, Sales

                            US, Civic, 10

                            US, Accord, 500

                             

                            Because for Country my sales are 510 and 600 and Japan doesn't meet the condition

                             

                            For Car, my output will be

                             

                            Country, Car, Sales

                            US, Civic, 10

                            Japan, Civic, 500

                             

                            Because for Car, my sales for civic is 510 and for Accord is 600.

                             

                            For Both Car and Country

                             

                            Country, Car, Sales

                            US, Accord, 500

                            Japan, Civic, 500

                             

                            I think you get my point....