Convert SQL case statement to qlik sense

[Kaushik2020]

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 ?

[jonashertz · Accepted solution]

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;