Forum Discussion

Adam13Hylo's avatar
Adam13Hylo
Helper I
9 years ago
Solved

Custom Column Query

Hi all,   Wondering if someone can help me please. I have trying to write a query so that if a column is less than 8 then this will combine a string and another column. However for some reason it i...
  • MattAllington's avatar
    MattAllington
    9 years ago

    You can't concatenation text with numbers. Convert the number first.

     

    Number.ToText([YearNum])

Adam13Hylo's avatar
Adam13Hylo
Helper I
9 years ago

Sorry Matt, 

 

My formula is now: 

if Number.ToText([ReportingMonthNum]) < "8" then "FY" & Number.ToText([YearNum]) else if Number.ToText([ReportingMonthNum]) >= "8" then "FY" & Number.ToText(Value.Add([YearNum], 1)) else ""

 

In the formula it should be that if the "ReportingMonthNum" is greater or equal to 8 then the value should be FY & the year number plus 1, this works for months 8 & 9 however it does not work for months 10, 11 or 12:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

It appears to be ignoring numbers with anything more than two digits. 

MattAllington's avatar
MattAllington
Community Champion
9 years ago

The issue is just after your second "if". You should not be using Number.ToText for the comparator, just for the concatenation. 

  • Adam13Hylo's avatar
    Adam13Hylo
    Helper I
    9 years ago

    Still does not work sorry, my query is now: 

    if [ReportingMonthNum] < "8" then "FY" & Number.ToText([YearNum]) else if [ReportingMonthNum] >= "8" then "FY" & Number.ToText(Value.Add([YearNum], 1)) else ""

     

    The Year Number column has a type of whole number as did the reporting month but I have changed this back to the default now.

     

    Column Values


    • MattAllington's avatar
      MattAllington
      Community Champion
      9 years ago

      Try changing Value.Add([yearnum],1) to simply [yearnum] + 1