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

[pam1990]

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)

[PriyankaShivhare]

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

[pam1990]

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]

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]

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]

Can you provide the 5-6 liner sample data

[pam1990 · Accepted solution]

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.