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

     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

      • 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.

  • 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.