6 Replies Latest reply on Feb 6, 2019 1:15 AM by Louise Le

    Max sum for dimension used to create concatenated field

    Ty Stackhouse
    Ty Stackhouse Feb 5, 2019 7:54 AM

      Hello,

       

      I am in a workbook (see attached) that has 3 fields: SPID, Product Description and Quantity Fulfilled.

      Each SPID has multiple product descriptions under it and each product has a number of quantity fulfilled.

       

      What I would like to do is be able to create a field that includes the product description concatenated with the SPID for the product description with the most quantity under that SPID.

      Currently, I have a LoD calculation: MAX({ fixed [GS_ProductDescription]:sum([QTYFULFI])}).

      This will give me the total for largest number of quantities fulfilled under that SPID. (See attached workbook)

       

      Here is the kicker... I would need to see the total quantity fulfilled at the SPID level

       

      What really need to see is would be something like this:

       

      SPID DescriptionSPID Total

       

      Pilocarpine Hydrochloride 4% Ophthalmic Solution-14

      		2010
      Verapamil Hydrochloride 40mg Tablet-19 4631
      Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20 2348

       

      The reason behind this is that SPID is just a number and is hard to identify. However, if we could have a product associated with that SPID (the most purchased one), we will know what type of products are under that SPID.

       

      THANKS!!

      Ty

        • 1. Re: Max sum for dimension used to create concatenated field
          Louise Le
          Louise Le Feb 5, 2019 8:25 AM (in response to Ty Stackhouse)

          Hi Ty,

           

          I'm having difficulty accessing your SQL Server so I cannot see your workbook. Could you provide some screenshots instead?

           

          Louise

          • 2. Re: Max sum for dimension used to create concatenated field
            Ty Stackhouse
            Ty Stackhouse Feb 5, 2019 8:34 AM (in response to Louise Le)

            Here is a workbook that uses an Extract. I will work on getting some screen shots for you.

            • 3. Re: Max sum for dimension used to create concatenated field
              Ty Stackhouse
              Ty Stackhouse Feb 5, 2019 8:36 AM (in response to Louise Le)

              Here is a screen shot of the two sheets that I have.

               

              • 4. Re: Max sum for dimension used to create concatenated field
                Louise Le
                Louise Le Feb 5, 2019 8:59 AM (in response to Ty Stackhouse)

                I think this workbook should solve your problem. I've created an LOD fixed at the GS_Spid instead and created a rank for each of the Product Descriptions. I then filtered to keep only rank 1 (i.e. most popular). As the LOD is fixed at the GS Spid level, the number will reflect the total, not the value of the specific Product Description.

                 

                Hope this is what you were looking for!

                 

                Louise

                • 5. Re: Max sum for dimension used to create concatenated field
                  Ty Stackhouse
                  Ty Stackhouse Feb 5, 2019 10:29 AM (in response to Louise Le)

                  Concatenated SPID

                  		Product Description

                  Pilocarpine Hydrochloride 4% Ophthalmic Solution -14

                  		 Pilocarpine Hydrochloride 4% Ophthalmic Solution
                  Pilocarpine Hydrochloride 4% Ophthalmic Solution -14Isopto Carpine 4% Ophthalmic Solution
                  Pilocarpine Hydrochloride 4% Ophthalmic Solution -14Pilocar 4% Ophthalmic Solution
                  Verapamil Hydrochloride 40mg Tablet-19Verapamil Hydrochloride 40mg Tablet
                  Verapamil Hydrochloride 40mg Tablet-19 Calan 40mg Tablet
                  Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)
                  Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20Vivelle Dot 0.025mg/24hr Transdermal Patch

                  Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20

                  		Alora 0.025mg/24hr Transdermal System
                  Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20 Minivelle 0.025mg/24hr Transdermal Patch
                  Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20Estradiol 0.025mg/24hr Transdermal Patch
                  Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20Vivelle 0.025mg/24hr Transdermal Patch

                  Thank you Louise Le, I never would have thought to do it that way.

                   

                  I think I can use this to try to move forward. The only thing that I have to figure out now is how to concatenate them into one field.

                   

                  I don't think that I explained the end goal very well in my original post.

                  Why I need them concatenated is so I have the ability to drill down to the individual product level (as shown)

                  • 6. Re: Max sum for dimension used to create concatenated field
                    Louise Le
                    Louise Le Feb 6, 2019 1:15 AM (in response to Ty Stackhouse)

                    Unfortunately I'm unsure of how to do exactly what you're looking for in one chart.

                     

                    However, what you could do is have two sheets. One with the Rank 1 Description (as in the workbook I attached, I'll call this chart 1 moving forward) and a second chart without the Rank on filters (so it shows all descriptions). Then, you can put the two sheets in a dashboard together and use chart 1 to filter chart 2 when you click on a GS_SPID using a dashboard filter action. This will allow you to look at a high level and then drill down to what you're interested in. A bonus of this is your dashboard won't be so cluttered as you'll only see what you specifically click on.

                     

                     

                    Hope this can solve your issue!

                     

                    Louise