Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

groups and binning

Hello,    I have list of month year in a table. (Jan 2018, Feb 2018, Mar 2018, Apr 2018, May 2018, June 2018, Jul 2018, Aug 2018, Sept 2018, Oct 2018, etc), I want to group it every 2 months, so, F...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, 

 

I don't have a date, it's just showing month and year. By using the second solution, it means I need to add in all possible combinations. What if I have all months from 2015 - up to anytime in the future? Is there any way to do this dynamically?

 

Thanks & Rgds, 

Lina

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Then you could do:

YourCalculatedColumn =
SWITCH(
  TRUE(),
  LEFT(< month column >, 2) = "Jan", "Feb " & RIGHT(< month column >, 4) ,
  LEFT(< month column >, 2) = "Feb", "Feb " & RIGHT(< month column >, 4),
  ...
)

If this solves the problem, please like it and mark it as solution.

 

Best regards,

Kristjan76

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI!, 

     

    I tried to use your first solution, because turns out the column is in date form, but it's not what I want to do. 

    so what happen is Jan and Feb = Feb

    March and April = March

     

    With this, I only have 6 data groups in a year, it's supposed to be 12. 

     

    but what I wanted is

    Dec and Jan = Jan

    Jan and Feb = Feb, 

    Feb and March = March

    March and April = April.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I created a table consists of 2 columns Grouping and Period. 

      Content of the data will be

      1. Grouping - Period
      2. Jan 2018 - Dec 2017
      3. Jan 2018 - Jan 2018
      4. Feb 2018 - Jan 2018
      5. Feb 2018 - Feb 2018
      6. Mar 2018 - Feb 2018
      7. Mar 2018 - Mar 2018
      8. and so on...

      Then match the period column with the same in other table, and group the chart based on Grouping column.

      It's a bit hardcoded, but it works for now...if anybody have a simpler way/more dynamic way to do this, I would appreciate. 

       

      Thanks & Rgds,

      Lina

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

        Please share a part of sample data so that we can test and coding formula on it.

         

        Notice: if you not have permission to upload sample file, please upload to onedrive or google drive and share link here.

         

        Regards,

        Xiaoxin Sheng