Issue in the given set expression

[ashima_0605]

Hi All,

I have to calculate the amount till the selected date in the quarter and if the day is not selected, then calculate till the current day from the start of the quarter.

I am able to calculate for single currency by formula

(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}BEACON_INR)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}BEACON_INR)/10000000
)

However, I have a filter pane with multiple currency types. We have defined the inline table in load script for the same

KPI_SELECTION:

Load * inline [

KpiID, Kpi

1, "BEACON USD"
2, "AMOUNT USD"

];

I defined a master measure for the same with below-mentioned code. But it is not giving me the expected results. Can you help

If (GetSelectedCount([Kpi])=0,
SUM({<Kpi={'AMOUNT USD'}>}Pick(KpiID,
(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}AMOUNT_USD)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}AMOUNT_USD)/10000000
)
))),

If (GetSelectedCount([Kpi]) > 1,
'Please select only one amount from Amount Panel',
Pick(KpiID,
(
if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}BEACON_USD)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}BEACON_USD)/10000000)
),

(
if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}AMOUNT_USD)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}AMOUNT_USD)/10000000)
)

)))

[rishikesh_suman]

Its good that this workaround is successful, however the code is not in optimized form. Depending on the dataset, the calculation will start to take more time.

[ashima_0605]

Hmm. So what should be the approach? I could not think of any other solution