topic Re: Case statement in Qlik in App Development

Case statement in Qlik

Kaushik2020 — Mon, 12 Jun 2023 12:56:39 GMT

I have below SQL statement which I am looking for an alternative in qlik sense.

count(distinct (case when (new_exist_flag='New' and (act_date between trunc(current_date-12,'MM') and current_date-12)) then co_id else null end))

I can use the count and distinct as part of Set expression from the UI.

so here i want to see a conversion of SQL statement - case when (new_exist_flag='New' and (act_date between trunc(current_date-12,'MM') and current_date-12)) then co_id else null end

-> columns from table:

new_exist_flag, act_date, co_id

thanks in advance

Re: Case statement in Qlik

Chanty4u — Mon, 12 Jun 2023 18:14:25 GMT

Try something like this

=count({<new_exist_flag={'New'}, act_date={">=$(=AddMonths(date(Today(), 'MM'), -12))<$(=date(Today(), 'MM'))"}>} DISTINCT co_id)

Re: Case statement in Qlik

Kaushik2020 — Tue, 13 Jun 2023 06:47:32 GMT

thanks for the reply. it worked with a small change.

~ Below shows previous month count. how we can modify this to qlik sense ?

count(distinct (case when ( (act_date between add_months(trunc(current_date-1,'MM'),-1) and add_months((current_date-1),-1))) then co_id else null end)) ,