Split year into months.

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Split year into months.


      a simple problem, which I can not solve.. I have yearly numbers from year 2021 to 2090 - and i need to split them into months (1/12) for each year.

      The only solution i have now is to hardcode rules with constant - so the rule looks like this:

      ['Measure':'Amount Month', , 'Time':{'2021-01', '2021-02', '2021-03', '2021-04','2021-05', '2021-06', '2021-07', '2021-08','2021-09', '2021-10', '2021-11', '2021-12'}] =
      B:([ 'Measure':'Amount Year', 'Time':'2021', ] / 12)

      But then I have to create 70 rules - one for each year..

      There must be a simpler way to do this :-). The optimal solution would be if i could manipulate the "left side" of the rule, but since i can not change this - i can not find a smart approach.

      All ideas are appreciated :)

    • Hi,

      as a starting Point i create an attribute on the year "MonthFormula" - which contains the "list" value.

      So 2020 has "2020-01', '2020-02', '2020-03', '2020-04', '2020-05', '2020-06', '2020-07', '2020-08', '2020-09', '2020-10', '2020-11', '2020-12" as month formula.

      Then i can create a Query formula, which returns attribute (2020-01', '2020-02', '2020-03', '2020-04', '2020-05', '2020-06', '2020-07', '2020-08', '2020-09', '2020-10', '2020-11', '2020-12) on left side and uses element (2020) on right side.

      But not a very efficient way.

    • Try to split values through "Pattern" in the query. So you do not have to create an attribute for every Dim Time element.

      ->You can use ([0-9]{4})-([0-9]{2}) regex in order to split monthes from the year. And substitute values in your rule accordingly. Let me know if it helped.
      • split.PNG

        (32.81 kB, downloaded 140 times, last: )
      In love with Jedox, since 2018 :)

      Best Regards / Mit freundlichen Grüßen
      Emil Abdullayev