Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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
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 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. 😥
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.
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))
Can you provide the 5-6 liner sample data
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.