-
1. Re: Max sum for dimension used to create concatenated field
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 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.
-
SPID LoD Calculation.twbx 36.3 KB
-
-
3. Re: Max sum for dimension used to create concatenated field
Ty Stackhouse Feb 5, 2019 8:36 AM (in response to Louise Le) -
4. Re: Max sum for dimension used to create concatenated field
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
-
SPID LoD Calculation (1).twbx 15.9 KB
-
-
5. Re: Max sum for dimension used to create concatenated field
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 -14 Isopto Carpine 4% Ophthalmic Solution Pilocarpine Hydrochloride 4% Ophthalmic Solution -14 Pilocar 4% Ophthalmic Solution Verapamil Hydrochloride 40mg Tablet-19 Verapamil Hydrochloride 40mg Tablet Verapamil Hydrochloride 40mg Tablet-19 Calan 40mg Tablet Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20 Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly) Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20 Vivelle 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)-20 Estradiol 0.025mg/24hr Transdermal Patch Estradiol 0.025mg/24hr Transdermal System (Twice-Weekly)-20 Vivelle 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 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