Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashima_0605
Contributor III
Contributor III

Issue in the given set expression

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

)))

Labels (1)
11 Replies
lorenzoconforti
Specialist II
Specialist II

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

sunny_talwar

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

ashima_0605
Contributor III
Contributor III
Author

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.

 

 

rishikesh_suman
Contributor
Contributor

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

rishikesh_suman
Contributor
Contributor

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

ashima_0605
Contributor III
Contributor III
Author

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.

 

lorenzoconforti
Specialist II
Specialist II

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

rishikesh_suman
Contributor
Contributor

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.

ashima_0605
Contributor III
Contributor III
Author

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