How to ignore selection in expression - Other that Set Analysis

[Anonymous]

Hi,

          I have problem in fetching data, for which i understood that selection, restricts a variable to get data, explaining the situation below.

          I have two selections in my Dashboard(Year and Scenario), which are related among each other.

In my dashboard, I kept three KPI's that captures the YTD data for three Scenario's: CurrentYearMIS, CurrentYearBUD and PreviousYearMIS respectively.

I have created three variables that captures, currentMIS, currentBUD and LastYearMIS based on selections.(Kept a KPI for each in the dashboard to confirm whether its captured properly).

      

In my requirement, I can select one value from only one filter at a time.

I have written my logic for calculation of each KPI based on its above related variables(Have two more static logic check one for period as its YTD and another for Account).

The KPI's logic is working fine, when I am selecting a scenario as shown below.(Values are dummy)

But when i am selecting Year, the Current Year MIS and Current Year BUD are captured correctly, but the previous year is not getting captured.

When i try to analyze the situation, i found that in selection of Year, only currentYearMIS and currentYearBUD are related(others highlighted in Grey). So the previous scenario (2013MIS in the above shown selection )value is not getting captured in my selection of Year( 2014).

The logic calculation of lastYearMIS is :

if(GetSelectedCount(YEAR) = 1,($(currentYear)-1)&'MIS',

  if(GetSelectedCount(SCENARIO_CODE) = 1,($(currentYear)-1)&'MIS','Unknown')

)

The logic calculation for LastYearMIS KPI is :

Sum({<[SCENARIO_CODE] = {'$(=$(lastYearMIS))'}, [PERIOD_CODE] = {'12'}, [ACCOUNT_CODE] = {'ACC_001'}>}AMOUNT)

Attaching the qvf also, I have created relevant variables in the app.

How can we resolve this selection restriction ??

[sunny_talwar · Accepted solution]

Try this:

Sum({<[SCENARIO_CODE] = {'$(=$(lastYearMIS))'}, [PERIOD_CODE] = {'12'}, [ACCOUNT_CODE] = {'ACC_001'}, YEAR>}AMOUNT)