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

[#1]

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 !

[swuehl]

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 )

[#3]

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 .

[swuehl]

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?

[#5]

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.

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  .

[ogster1974]

I've tackled this kind problem in my own data model.  The Master calendar i've left hanging on its own and created a separate calendar to support each of my dimension/fact tables that need one.

I have then used set analysis to join my dimension calendar to the master calendar only when needed.

Its more work to code but I have full control of my date filtering as a result. 

[#7]

So how would you use set analysis to join the dimension ? can you give an example ?

Thank you

[ogster1974 · Accepted solution]

So in my case I have 2 calendars to make this work

1. for my dimension which is joined.  In your example a WinLossCalendar.  You create one for each dimension you are looking to control this way.  Name the values of your calendar uniquely to avoid synthetic keys.

2. A master calendar that's not attached to any tables directly.  This is the calendar I display in my dashboard to the user.

If I include a visualisation that needs to be linked to the calendar I insert the following set analysis in my measure.

{< [%WinLossYYYYMM]=p([%YYYYMM]) >} I join on YYYYMM key you use a date key but the principle's the same.

Using this principle you can see I can easily wire in as many dates as I want to run off one click from a user.

Hope this makes sense.

Regards

Andy

[ogster1974]

PLease mark any replies That have been helpfully, correct to help other users of the community.  Thanks

ANdy

‌