topic Convert SQL case statement to qlik sense in App Development

Convert SQL case statement to qlik sense

Kaushik2020 — Sun, 18 Jun 2023 07:30:12 GMT

I have a scenario in SQL where i am using below case statements.

count(distinct (case when ( (Reg_date between trunc(current_date-1,'MM') and current_date-1)) then con_id else null end)) New,
count(distinct (case when ( (Reg_date between add_months(trunc(current_date-1,'MM'),-1) and add_months((current_date-1),-1))) then con_id else null end)) Old,

Can anyone please suggest what will be the replacement of these in Qliksense ?

Re: Convert SQL case statement to qlik sense

jonashertz — Mon, 19 Jun 2023 09:19:29 GMT

Let's see if I got this right this time. use monthstart, addmonths and today functions edit as needed if I didn't get it right. Also beware that Reg_date is formatted as a date otherwise use the date or date# functions.

result:
Load
count(distinct if(monthstart(today()-1) <= Reg_date and Reg_date <= today()-1, con_id)) as New,
count(distinct if(monthstart(addmonths(today()-1,-1)) <= Reg_date and Reg_date <= addmonths(today()-1,-1), con_id)) as Old
Resident source_table;