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 then group A

If date between 08-10-2019 and 29-10-2019 then group B  ** Here i want the overlapping dates from group A as well.

If date between 15-10-2019 and 05-11-2019 then group C and So on.  ** Here i want the overlapping dates from group A and group B as well.

 

 

I have tried the IF, OR, AND logic but it is just working for the first group.

 

Please let me know if anybody have any other function in your mind which could solve this issue.

7 Replies

  • 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

       Hi Anonymous 

       

      what value you are expecting for 16-10-2019?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI Anonymous  I'm working with Sales data and here I want cumulative week as following :

         

        So, Group "A" will show sales from 1, Oct 2019 to 22,Oct 2019

              Group "B" will show sales from 8, Oct 2019 to 29,Oct 2019 ** Here I want sales of last three weeks from group "A"

              Group "C" will show sales from 15, Oct 2019 to 05,Nov 2019 ** Here I want sales of from 15 till 05

              Group "D" will show sales from 22, Oct 2019 to 12,Nov 2019 and so on.

         

        I will use these groups as a slicer or in bar chart to show/ filter sales according to week bucket.

    • 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

    • spandy34's avatar
      spandy34
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      Hi

       

      I have a similar query but I was wondering if, instead of having to update the values each year, we could set a start date say 11/10/21 and then every 28 days a new Period - like the example below.  I need them as period numbers based on a DateofVIsit value - and then for it to start back at Period 1 for the next year after 13 Periods.