Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
)
)))
Can you post the application so that we can have a proper look at it?
What is it giving as an output and what do you expect to see?
Hi.
I cant share the app file.
As per my use-case, I have defined 2 dimensions in filter pane.
1) 'Day Number' (Days listed from 1-31)
2) 'Amount Selection'. (Amount types: beacon usd and amount USD)
if the user selects a 'Day Number', my graph should show the amount aggregated till that day. eg: if the user selects day 5, The amount from Day 1 to Day 5 will be added. The amount, in this case, will be a default amount type(amount usd).
However, if the user selects 'Amount Selection' - beacon USD from filter pane and Day Number - 5, the graph should calculate amounts from Day 1 to Day5 from beacon usd amount column.
I was able to calculate amounts with only Day Number selection
(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}AMOUN_USD)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}AMOUNT_USD)/10000000
)
For Amount Selection, I had defined an inline table
KPI_SELECTION:
Load * inline [
KpiID, Kpi
1, "BEACON USD"
2, "AMOUNT USD"];
and then i was trying to use pick function
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)
)
)))
But with this script, if I select usd amount in Amount selection. The graph is not displaying inr amounts. Its blank in all cases.
Hello Ashima,
Whatever may be currencies in the Inline Table, you can get the correct result.
Step1:
Make a variable which will return the default currency if no currency is selected. Else it should return the selected Currency.
Example:
Create a variable "v_Currency".
Expression will be:
if (getselectedcount(KpiID)=0, 'your default currency name/ID', getfieldselection(Kpi))
Step 2:
Use this variable "v_Currency" in your Set Expression in the place where you are specifying the Currency_Type.
Example:
Your code was:
(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}AMOUN_USD)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}AMOUNT_USD)/10000000
)
New Code after using the above created Variable will be:
(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}$(v_Currency))/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}$(v_Currency))/10000000
)
Step 3:
Limit the Currency Selection with "Always one Selected Value" so that Users may not be able to select the multiple Currencies at a time.
Hope this works...!!!
Please Note: I have assumed that your expression with one Currency was working correctly.
Regards,
Rishikesh
Hi Ashima,
You can use the multi currency selection with the below steps:
Step1: Create a new Variable "v_Currency" and write the below expression inside it
if(getselectedcount(KpiID)=0,'Your Default Currency', Kpi)
Step2:
Use the newly created variable in your expression:
Your original Expression:
(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}BEACON_INR)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}BEACON_INR)/10000000
)
New Expression using the new created Variable:
(if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}$(v_Currency))/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}$(v_Currency))/10000000
)
Step3:
Apply the feature "Always one Selected " in the list box so that you can restrict the users to select multiple currencies at a time.
Hope this works...!!!
Please Note: I have assumed that your original expression is working correctly with any of the Currencies
Regards,
Rishikesh
Sorry. I did not work.
the variable is not working even if I put it in independent KPI graph.
It works only if we put code as 'getfieldselections(Kpi)'. This displays blank without selection and the selected amount on selection from filter pane.
Even when I use $(v_Currency) in my code in graph, it is not displaying the graph on selection of currency from filter pane.
Try using Rishikesh solution but update your inline table to match exactly the field name (including the underscores):
KPI_SELECTION:
Load * inline [
KpiID, Kpi
1, "BEACON_USD"
2, "AMOUNT_USD"
];
I understand you can't post your entire application but try a subset of it. It really hard to understand what is going on without seeing it first hand
Hi Ashima,
Can you give your expressions and Inline Table Code and Field Names which should be selected when either of the Currencies are selected?
Please note that the field names corresponding to each currency in Inline table should be derived in script before hand.
Inline Table Code:
KPI_SELECTION:
Load * inline [
KpiID, Kpi
1, "BEACON USD"
2, "AMOUNT USD"
];
My column name in DB is with an underscore . So the table has column names as BEACON_USD and AMOUNT_USD
Independent code that is running is:
if(GetSelectedCount(dateNumber)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}BEACON_USD)/10000000,
Sum({<[FLAG]={"ABC","DEF"},dateNumber={"<=$(=CurrDayNo)"}>}BEACON_USD)/10000000)
Front end filter panes are follows: Default currency to appear is Amount USD
DateNumber Amount Selection
1- 31 BEACON USD
AMOUNT USD
So if the user selects "BEACON USD" from the front end, then the DB's column that should be selected is BEACON_USD
However, I tried a bit more, so this code worked
if(GetSelectedCount(dateNumber)>0 and GetSelectedCount(Kpi)=0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}AMOUNT_USD)/1000000,
if((GetSelectedCount(dateNumber)>0) and GetSelectedCount(Kpi)>0,
Sum({$<dateNumber={">=$(=0)<=$(=GetFieldSelections(dateNumber))"}>}Pick(KpiID,
BEACON_USD,AMOUNT_USD)/1000000,
if((GetSelectedCount(dateNumber)=0) and GetSelectedCount(Kpi)>0,
Sum({<dateNumber={"<=$(=CurrDayNo)"}>}Pick(KpiID,
BEACON_USD,AMOUNT_USD))/1000000,
Sum({<dateNumber={"<=$(=CurrDayNo)"}>}AMOUNT_USD)/1000000)
))