Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Grouping Dates

So I’m working with date-data and want to make a new column (I will use this as a slicer in my report) to group dates according to following condition : If date between 01-10-2019 and 22-10-2019 th...
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

Try to SWITCH, it will stop execute after first succesfull conditionand will help you to resolve overlapping issue

DateGroup = SWITCH(TRUE(),
DATESBETWEEN([Date], DATE(2019,10,1), DATE(2019,10,22)), "A",
DATESBETWEEN([Date], DATE(2019,10,8), DATE(2019,10,29)), "B",
DATESBETWEEN([Date], DATE(2019,10,15), DATE(2019,11,5)), "C",
"Undefined"
)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I'm getting the above error while using SWITCH(). 

 

az38 

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Sorry, Anonymous 

    I gave you completely wrong statement

    try

    DateGroup = SWITCH(TRUE(),
[Date] >= DATE(2019,10,1) && [Date] <=  DATE(2019,10,22)), "A",
[Date] >= DATE(2019,10,8) && [Date] <=  DATE(2019,10,29)), "B",
[Date] >= DATE(2019,10,15) && [Date] <=  DATE(2019,11,5)), "C",
"Undefined"
)

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi az38  Anonymous 

       

      let say date is 2019/10/16. 

      this date comes in all buckets but as per column it will always show "A" only. The first condtion of switch statement get satisfied and it will not check for next conditions.

       

      Anonymous  if you want the same solution then above solution will work.

       

       

      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.