8 Replies Latest reply: Jan 26, 2017 11:55 AM by Carlos Londono RSS

    Max week in quarter-Current week

    Carlos Londono

      Hello community,

       

      I have the following challenge. The inventory should be the amount in the max week in the quarter. If it is in the current quarter, the inventory amount should be the current week's inventory amount.

       

      My inventory table uses weekly snapshots.  The quarter inventory amount should be the amount of the snapshot of the quarter’s last week.  I am using a max week in quarter flag (MaxWeek_Flag = 1 if week is max week in quarter).

      Using this formula:

       

      Sum({<MaxWeek_Flag={1}>}Inventory)

       

      It gives me the values except for the current quarter (20171)

      1.png

      This other formula gives me correct values except for the last quarter, it aggregates all the weeks in the last quarter instead of returning the current week as the max week in the quarter:

       

      if(Sum({<MaxWeek_Flag={1}>}Inventory)=0,

           Sum({<SnpshtYYYYWK={"<=$(cCurrent_Week_YYYYWK)"}>}Inventory),

                Sum({<MaxWeek_Flag={1}>}Inventory))

      2.png

       

      Any ideas how to achieve this?

       

      Thanks for your help

       

      Carlos

        • Re: Max week in quarter-Current week
          Wallo Atkinson

          have you tried

          sum(Inventory*MaxWeek_Flag)

           

          Or

          sum(if(SnpshtYYYYWK=aggr(max(SnpshtYYYYWK),SnpshtYYYYQ),Inventory,0))

          • Re: Max week in quarter-Current week
            Aehman K

            Hi,

            I've posted Master Calendar Script in community, just follow this link.

            You'll need to insert the script attached in your Data Model or/source.

             

            Master Calendar Script

             

            before reloading Master Calendar add 2 extra syntax to the script as follows.

             

            In normal load paste this

            if(InWeek(TempDate,today(),-1),1,0) as CQW,

            and In preceding load I.E before the Load Statement paste

            if(CQW=1 and len(QuarterEnd([Effective Date]))>0,1,0) as CQMW,

            And in front end use CQMW as flag

            Sum({<CQMW={1}>}Inventory)


            CQMW will give you Max week for current quarter (01-09-2017 to 01-15-2017) as this week has not yet finished so it will show previous week as Max.



            • Re: Max week in quarter-Current week
              Sunny Talwar

              Will you be able to share a sample or sample data to look at what you have?

                • Re: Max week in quarter-Current week
                  Carlos Londono

                  Hi Sunny,

                   

                  Attached is a  QVW with sample data.  I realized I posted thread to QlikSense and actually it is a QlikView question.  Let me know if I should close it and open in QlikView.

                   

                  Thanks,

                  Carlos

                    • Re: Max week in quarter-Current week
                      Sunny Talwar

                      1 option would be to use this expression:

                      FirstSortedValue(Aggr(Sum(Inventory), Business, YYYYQ, YYYYWK), -Aggr(YYYYWK, Business, YYYYQ, YYYYWK))

                       

                      Capture.PNG

                       

                      2nd option would be to to add a MaxWeek Flag to 201702 in the script may be using a logic like this

                       

                      Table:

                      LOAD Business,

                                YYYYQ,

                                YYYYWK

                      FROM....

                       

                      Left Join (Table)

                      LOAD Business,

                                YYYYQ,

                                Max(YYYYWK) as YYYYWK,

                                1 as MaxWeek_Flag

                      Resident Table

                      Group By Business, YYYYQ;

                       

                      Once you do this, you should be able to use just this expression without any if statement

                      Sum({<MaxWeek_Flag={1}>}Inventory)

                       

                      3rd option would be to use this

                      if(Sum({<MaxWeek_Flag={1}>}Inventory)=0,

                          Sum({<YYYYWK={"$(=Max(YYYYWK))"}>}Inventory),

                                Sum({<MaxWeek_Flag={1}>}Inventory))

                        • Re: Max week in quarter-Current week
                          Carlos Londono

                          Thanks for your help Sunny,  If I am using a cyclic group with dimensions such as Region, Country, Division, Business, Product, etc. should I modifiy the expression to aggregate at the highest level or how should I address that issue?  For example:

                           

                          FirstSortedValue(Aggr(Sum(Inventory),Region, YYYYQ, YYYYWK), -Aggr(YYYYWK, Region, YYYYQ, YYYYWK))


                          Your recommended solution works when I have a single dimension, but it breaks if I use a cyclic group or a table with multiple dimensions. If I use your recommended 2nd option, should I aggr at the highest level like this?

                           

                          Table:

                          LOAD Region,

                                    YYYYQ,

                                    YYYYWK

                          FROM....

                           

                          Left Join (Table)

                          LOAD Region,

                                    YYYYQ,

                                    Max(YYYYWK) as YYYYWK,

                                    1 as MaxWeek_Flag

                          Resident Table

                          Group By Region, YYYYQ;