6 Replies Latest reply on Sep 12, 2016 6:20 AM by emma.villarreal

    How to get the first  field

    emma.villarreal

      Hello, I have the next question I have the next table

      Captura de pantalla 2016-09-09 a la(s) 6.08.14 p.m..png

      thats the information of that period there is more behing so if I use Min(delivery date) I get the date before the period i am filtering so then I used this:

       

      LOOKUP(MIN([Delivery Date]),0)

       

      becasue I need just the first Current_Qty from every period I filter like this

      Captura de pantalla 2016-09-09 a la(s) 6.08.18 p.m..png

       

      how can I do this ? I tried Fixed but is not getting me the date I want

        • 1. Re: How to get the first  field
          Andrew Watson

          You have 2 options:

           

          1. FIXED. IF [Date] = {FIXED [MaterialId]:MIN([Date])} THEN [Current_Qty] END

           

          Put this on to the Filters shelf, Relative Dates - Special - Non null values

           

          2. INDEX (this is more complicated). Create a field INDEX()

           

          You need to adjust the advanced settings so it restarts every MaterialId. Then it'll return a 1 for the first record for each MaterialId, 2 for the second record, etc. Drag it to the Filters shelf and filter for a MIN and MAX of 1.

          • 2. Re: How to get the first  field
            emma.villarreal

            thanks for ur answer but what should I put in the filter ?  this {FIXED [MaterialId]:MIN([Date])}??

            I tried that but the field is in blank

            • 3. Re: How to get the first  field
              Bora Beran

              You can put IF [Date] = {FIXED [MaterialId]:MIN([Date])} THEN [Current_Qty] END on filter shelf as Andrew suggested and keep the non null values or use a shortened version i.e.

               

              [Date] = {FIXED [MaterialId]:MIN([Date])}

               

              on filter shelf and keep True.

               

              In either case using Current_Qty in a view will give you only the earliest value for each MaterialID since the filter will exclude everything else.

              • 4. Re: How to get the first  field
                emma.villarreal

                Thanks for ur asnwe but is al nulls and show nothing, its maybe because its taking the min date of the data and not of the period i am filtering I am attaching the workbook and the field is initial inventory

                • 5. Re: How to get the first  field
                  Bora Beran

                  Thanks for sharing the workbook, it helps a lot.

                   

                  As you guessed, the issue was with the filters. {fixed calculations happen before dimension filters so it was finding the first date, that's outside the range of other filters you have in the viz, so when the other filters kicked in you're ending with nothing.

                   

                  You can change the calculation to this

                   

                  IF [Delivery Date] = {EXCLUDE [Nombre]:MIN([Delivery Date])} THEN [Current Qty] END

                   

                  And use this viz setup.

                   

                  The calculation will still compute per MaterialID since Nombre, MaterialID are the two dimensions in the sheet and by excluding Nombre you end up with only MaterialID.

                   

                  This makes initial inventory a measure hence it is being used as MIN(initial inventory). Since the calculation computes for only the first date (and in your data there doesn't seem to me more than 1 event at the same date) min/max/attr.... all give the same result.

                   

                  Screen Shot 2016-09-11 at 11.21.25 PM.png

                  • 6. Re: How to get the first  field
                    emma.villarreal

                    Thanks a lot!!! u save me thats the answer I wanted thanks