8 Replies Latest reply: Dec 29, 2015 7:42 AM by Andy Weir RSS

    Set Analysis with Default Period and excluding other tables and their demensions

    Tigran Novosartov

      Hello Everyone ,

      Im trying to do some complex set analysis it looks very simple but doesn't work . Please help if you can or tell me if its impossible to do

      I have around 7 tables that all connected to Master Calendar table that is the only link between all of them .

       

      So the set analysis below , does sum and the portion where you see [$)=Concat .... and then list of tables , it allows you to ignore all of the selection from those tables. So if you pick field from one of those tables the chart/object will ignore selection as long as that table is listed in set analysis .

       

       

      #1This script Doesn't work: (im trying to do the same thing i do in #2 but also set default year )

      if(isnull(getfieldselections(FiscalYearWinnLoss)),

      Sum({$<FiscalYearWinnLoss={'$(=MAX(FiscalYearWinnLoss))'},[$(=Concat({1<$Table='CareInfo','CustomerExperience','CustSapAndHPCust','DailySales','ITMetrics','pro_tran','ServiceLevel','TurnOver'}>}distinct $Field,']=,[')&']=')>}[Amount(New)])/1000000,SUM([Amount(New)])/1000000 )

       

      #2 The following script works for another object that ignores different set of tables.

      sum({$<[$(=Concat({1<$Table={'CareInfo','CustomerExperience','WinLoss','DailySales','ITMetrics','pro_tran','ServiceLevel','TurnOver'}>}distinct $Field,']=,[')&']=')>} Total_Revenue_GP)/1000000

       

      Thank you !

        • Re: Set Analysis with Default Period and excluding other tables and their demensions
          Stefan Wühl

          Isn't there an opening curly bracket { missing before 'CareInfo'?

           

          if(isnull(getfieldselections(FiscalYearWinnLoss)),

          Sum({$<FiscalYearWinnLoss={'$(=MAX(FiscalYearWinnLoss))'},[$(=Concat({1<$Table={'CareInfo','CustomerExperience','CustSapAndHPCust','DailySales','ITMetrics','pro_tran','ServiceLevel','TurnOver'}>}distinct $Field,']=,[')&']=')>}[Amount(New)])/1000000,SUM([Amount(New)])/1000000 )

            • Re: Set Analysis with Default Period and excluding other tables and their demensions
              Tigran Novosartov

              Yes it is . Even when you put the bracket its the same story for some reason it ignores FiscalYearWinnLoss={'$(=MAX(FiscalYearWinnLoss) .. portion of set analysis .

              It seem that it does second part and ignores my selections but doesn't sets to Max(fiscalYearWinLoss ) by default .

              I checked if only do set analysis to set to default fiscal year that works but not when combining with another one .

                • Re: Set Analysis with Default Period and excluding other tables and their demensions
                  Stefan Wühl

                  It's hard to help with the information provided. For example, in which table is FiscalYearWinnLoss located? And what is the format of its values?

                    • Re: Set Analysis with Default Period and excluding other tables and their demensions
                      Tigran Novosartov

                      FiscaleYearWinnLoss field is in WinLoss table it contains values 2013, 2014, 2015 and 2016 .

                      After playing around more now it works fine for default fiscal year and it does ignores selection from other tables but not from all ..

                      It strange if i make some selection from DailySales table it ignores it but if select something from CareInfo table then it changes the values which i don't want it to do  .

                      I added my DM picture below each table has many fields.

                      DM_EXECUTIVE.png

                      This is my latest expression , it sets to default fiscal year 2016 and it ignores selection but not from all tables like I mentioned above if Select some values in CareInfo table it changes value .

                       

                      if(isnull(getfieldselections(FiscalYearWinnLoss)),Sum({$<FiscalYearWinnLoss={'$(=MAX(FiscalYearWinnLoss))'},[$(=Concat({1<$Table='CareInfo','CustomerExperience','CustSapAndHPCust','DailySales','ITMetrics','pro_tran','ServiceLevel','TurnOver'}>}distinct $Field,']=,[')&']=')>}[Amount(New)])/1000000, 

                      Sum({<[$(=Concat({1<$Table='CareInfo','CustomerExperience','CustSapAndHPCust','DailySales','ITMetrics','pro_tran','ServiceLevel','TurnOver'}>}distinct $Field,']=,[')&']=')>}[Amount(New)])/1000000)

                       

                       

                       

                      Im thinking maybe its  logically not possible since i'm ignoring all table except calendar and they all connected in "Calendar' table.I still want user be able select dates and apply that across all tables but not any other field selection . So maybe even tho i'm ignoring all tables since i'm not ignoring Calendar it somehow still applying selection when i select some filed.  For Example if select  Sales Person that only has sales for 2013 and that automatically applies filter in "Calendar" table for 2013 only since the all connected it gets messed up  .