topic Re: Issue in the given set expression in App Development

Issue in the given set expression

ashima_0605 — Sat, 16 Nov 2024 03:44:41 GMT

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

)))

Re: Issue in the given set expression

lorenzoconforti — Mon, 23 Dec 2019 12:15:22 GMT

Can you post the application so that we can have a proper look at it?

Re: Issue in the given set expression

sunny_talwar — Mon, 23 Dec 2019 13:49:11 GMT

What is it giving as an output and what do you expect to see?

Re: Issue in the given set expression

ashima_0605 — Tue, 24 Dec 2019 06:50:49 GMT

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

)))

But with this script, if I select usd amount in Amount selection. The graph is not displaying inr amounts. Its blank in all cases.

Re: Issue in the given set expression

rishikesh_suman — Tue, 24 Dec 2019 07:56:01 GMT

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

Re: Issue in the given set expression

rishikesh_suman — Tue, 24 Dec 2019 09:01:53 GMT

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:

New Expression using the new created Variable:

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

Re: Issue in the given set expression

ashima_0605 — Tue, 24 Dec 2019 10:49:11 GMT

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.

Re: Issue in the given set expression

lorenzoconforti — Tue, 24 Dec 2019 11:07:22 GMT

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

Re: Issue in the given set expression

rishikesh_suman — Tue, 24 Dec 2019 11:13:56 GMT

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.

Re: Issue in the given set expression

ashima_0605 — Tue, 24 Dec 2019 12:20:48 GMT

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

Re: Issue in the given set expression

rishikesh_suman — Tue, 24 Dec 2019 12:31:16 GMT

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.

Re: Issue in the given set expression

ashima_0605 — Thu, 26 Dec 2019 10:20:35 GMT

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