21 Replies Latest reply: Apr 25, 2018 11:00 AM by Sunny Talwar RSS

    Value List within Set analysis

    Charles Litten

      I am trying to create a custom table. I've made the dimension

       

      =ValueList('TEST1','TEST2')


      The measures for this are all very similar, but distinctly different. In the final version there will be FORTY different values in the list, so I'm trying not to make 40 different formulas. (I've already made it, it runs very slowly)


      Ideally I'd make a measure like this


      Sum({<

      [Landing Page Path]={"=$(=pick(match(ValueList('TEST1','TEST2'),'TEST1','TEST2'),{"*/Test1*"},{"*/Test2*"}))"},

      Date=

      >}Sessions)

       

      I can get it to work like this

       

      pick(match(ValueList('TEST1','TEST2'),'TEST1','TEST2'),

      Sum({<

      [Landing Page Path]={"*/Test1*"}

      Date=

      >}Sessions)

      ,

      Sum({<

      [Landing Page Path]={"*/Test2*"}

      Date=

      >}Sessions))

       

      but like I said as I start to grow to over 40 different values in the table, it REALLLLLLY slows down. I'm trying to simplify this formula

       

      Thanks!

        • Re: Value List within Set analysis
          Sunny Talwar

          What you are trying to do is not going to work.... its just not how set analysis really works... why don't you create a new field in the script from your [Landing Page Path] and use it as one of the dimensions in the chart?


          Pick(WildMatch([Landing Page Path], '*/Test1*', '*/Test2*', .....), 'TEST1', 'TEST2',....) as NewField


          and now use this as a dimension with this expression

          Sum(Sessions)

            • Re: Value List within Set analysis
              Charles Litten

              I've thought about doing that but there is often some overlap.

               

              For example one row in the table may be a combination of '/test2' + '/test6'.

                • Re: Value List within Set analysis
                  Sunny Talwar

                  May be a link table then using a for loop

                   

                  LinkTable:

                  LOAD [Landing Page Path],

                       'TEST1' as NewField

                  Resident ....

                  Where WildMatch([Landing Page Path], '*/Test1*');

                   

                  Concatenate (LinkTable)

                  LOAD [Landing Page Path],

                       'TEST2' as NewField

                  Resident ....

                  Where WildMatch([Landing Page Path], '*/Test2*');

                   

                  and so on....

                   

                  Again, don't right them 40 times, but may be create a for loop to do this...

                    • Re: Value List within Set analysis
                      Charles Litten

                      Thanks, this already seems like a big improvement. Sorry for the late reply, but any way you can help with the for loop?

                       

                      maybe a similar example

                       

                      thanks again

                        • Re: Value List within Set analysis
                          Sunny Talwar

                          Do you have the list if NewField values stored in an Excel file or something?

                            • Re: Value List within Set analysis
                              Charles Litten

                              This is essentially what I need the table to look like (this is half of it) Each Line in the field is a sum of all the landing pages in the same row on the right. Some are completely independent. But others, like the 2nd last row, are a sum of original values and of other rows. I've written each one out individually(for just these 20) using the code you wrote above and the load time is about 8 minutes (I definitely need to get that lower)

                               

                              New FieldLanding Page Path
                              Test1/test1
                              Test2/test2
                              Test3/test3a/test3b
                              Test4/test4
                              Test5/test5
                              Test6/test6
                              Test67/test6/test7
                              Test7/test7
                              Test8/test8a/test8b
                              Test9/test9
                              Test10/test10
                              Test11/test11a/test11b/test11c
                              Test12/test12a/test12b/test12c/test12d/test12e/test12f
                              Test13/test13
                              Test14/test14
                              Test15/test15
                              Test16/test16
                              Test17/test17
                              Test100/test100a/test14/test9/test100b/test100c/test18
                              Test18/test18