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)
)
)))
