2 Replies Latest reply: Jan 3, 2016 10:00 AM by Sangram Reddy RSS

    Use annual and quarterly approach in expression

      I have this scenario:

       

      I want to have two approach in my expression. First is Annual Approach, second is quarterly approach. For example, If the quarter of "PIN 123" is 1-4, sum(Annual Approach Expression). If the quarter is 1-3 or the whole quarter of the year is incomplete, sum(quarterly approach expression).


      I have the expression working properly for annual approach and quarterly approach. The problem is I want to identify whether each data per Per quarter is 1-4 or 1-3/2-4 . For example

       

      if (quarter per PIN is 1-4) then {

      //Annual Approach } else {

      //Quarterly Apprach }

       

      I attach my qvf file below. PLease see it for yourself. Thank You and Happy Holidays!

       

       

       

      1. PIN           OWNERNUM          QUARTER          PENALTY 
      2. 123              1000             1               40.00 
      3. 123              1000             2               40.00 
      4. 123              1000             3               40.00 
      5. 123              1000             4               40.00 
      6. 456              1000             1               20.00 
      7. 456              1000             2               20.00 
      8. 456              1000             3               20.00 

       

      by the way the name of app is practice2..

        • Re: Use annual and quarterly approach in expression
          Oleg Troyansky

          Hi Mark,

           

          You could solve this problem with Advanced Aggregation by PIN. Something along these lines:

           

          sum(

              AGGR(

                          IF(Count(distinct Quarter) = 4, <Annual formula>, <Quarterly formula>,

                         , PIN, [all other chart dimensions]

               )

          )

           

          I could show it to you in your app if you could tell me on what sheet/chart to apply it, and what are the two formulas that you'd like to use.

           

          As a side comment - your app really needs some data modeling help. It has 7 levels deep Synthetic keys, and because of that the app is extremely slow. A tighter data model could improve performance of your app by a lot. I recommend engaging an expert who can help you improve the data model. I can help, if you'd like me to.

           

          cheers,

          Oleg Troyansky

          Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

          • Re: Use annual and quarterly approach in expression
            Sangram Reddy

            Hi Mark,

             

            Make use of : inyeartodate

                                  inQuartertodate

                                  inmonthtodate functions to set flags and make use of these flags to construct your expressions using set analysis. Doing so, you will even have an optimised performance in your application.

             

            Thanks and Regards,

            Sangram Reddy.