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: 
pam1990
Contributor III
Contributor III

error in set modifier ad hoc element list ' ' or ')' expected with count and multiple conditions

I am still learning how to do set analysis so what I have is based on looking through several responses to this particular error.

I creating a KPI. It is the type that I want to change based on selections so I don't want it to be static.

The KPI is meant to count the number of deal-loans where the sum of the suspense amount is not zero and the period date is equal to the period date parsed out of the oanNumber_PeriodDate.

 

Count({<DealId_LoanNumber={"=Sum(SuspenseAmt) <> 0",period_date={'Date(right(LoanNumber_PeriodDate,10),"YYYY-MM-DD")'}>} distinct DealId_LoanNumber)

Labels (3)
1 Solution

Accepted Solutions
pam1990
Contributor III
Contributor III
Author

Thanks for the suggestions. I went the route of changing the SQL code to identify the loans in suspense, then I used an if then statement to accomplish what I needed.

View solution in original post

7 Replies
PriyankaShivhare
Creator II
Creator II

Count({<DealId_LoanNumber={"=Sum(SuspenseAmt) > 0"},period_date={'$(=Date(Date#(right(LoanNumber_PeriodDate,10),'YYYY-MM-DD'),'YYYY-MM-DD'))'}>} distinct DealId_LoanNumber)

You have missed the closing curly bracket in DealId_LoanNumber 

Assuming that u have date string so converting it to date format

 

MayilVahanan

Hi @pam1990 

Try like below.

Count({<DealId_LoanNumber={"=Sum(SuspenseAmt) <> 0"},period_date={"$(=Date(right(LoanNumber_PeriodDate,10),'YYYY-MM-DD'))"}>} distinct DealId_LoanNumber)

If its string, use with Date# function

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
pam1990
Contributor III
Contributor III
Author

Thanks for the response @PriyankaShivhare  and @MayilVahanan. The count is returning as zero.

I did a test using =if(period_date=Date(right(LoanNumber_PeriodDate,10),'YYYY-MM-DD'),1,0). The result for the desired rows is 1 so it seems that the Date(right(LoanNumber_PeriodDate,10),'YYYY-MM-DD') portion is working.

Now I'm stuck. I don't know enough about Qlik Sense or set analysis to know what I'm doing wrong. 😥

pam1990
Contributor III
Contributor III
Author

The LoanNumber_PeriodDate field format is 9999999_YYYY-MM-DD. The above Date(right(LoanNumber_PeriodDate,10),'YYYY-MM-DD' statement is extract the date from the string and compare it to a date field.

tm_burgers
Creator III
Creator III

I think that your Sum is the issue:

 

I would write as 

if(AGGR(Sum({<period_date={"$(=Date(right(LoanNumber_PeriodDate,10),'YYYY-MM-DD'))"}>} SuspenseAmt),DealId_LoanNumber) <> 0, count(distinct DealId_LoanNumber))

 

 

 

PriyankaShivhare
Creator II
Creator II

Can you provide the 5-6 liner sample data

pam1990
Contributor III
Contributor III
Author

Thanks for the suggestions. I went the route of changing the SQL code to identify the loans in suspense, then I used an if then statement to accomplish what I needed.