Issue with with Pivot table dimenion

[AC]

Hello,

    I am trying to create a new  field in Pivot table dimension from an existing field, I have tried using if match and nested if as well but nothing is working not sure what the problem is, here is what I am trying to do.

I have a LastReportedBucket filed with the values (1 DAY, 2-7 DAYS, 8-30 DAYS, 31+ DAYS and Never) from this I want to create a filed in Pivot table dimension like below.

1 DAY and 2-7 DAYS to 1-7 DAYS

8-30 DAYS and 31+ DAYS to 7+ DAYS

Nerver to Nerver

I was trying below expression and its not showing any error in the expression window but I am not getting anything in the Pivot table

=if(Match(LastReportedBucket, '[1 DAY]','[2-7 DAYS]'),'[1-7 DAYS]',

if(Match(LastReportedBucket, '[8-30 DAYS]','[31+ DAYS]'), '[7+ DAYS]',

if(Match(LastReportedBucket,'NEVER','NEVER'),LastReportedBucket)))

I have similar issue with another field called ReportingBucket and the the values are 1 DAY, 2 DAYS, 3 DAYS.....30 DAYS from this i was trying to display first 7 DAYS (1 DAY, 2 DAYS...7 DAYS) using if match here also same issue.

[wdchristensen]

=if(LastReportedBucket='[1 DAY]' OR LastReportedBucket='[2-7 DAYS]', '[1-7 DAYS]',

     if(LastReportedBucket='[8-30 DAYS]' OR LastReportedBucket='[31+ DAYS]', '[7+ DAYS]',

          if(LastReportedBucket='NEVER', 'NEVER', 'UNCATEGORIZED WARNING')))

[d_prashanthredd]

Hi Anil,

Why you need square ([ ]) brackets in single quotes? Ignore them and check it.

Thanks,

Prashanth Reddy D.

[neelamsaroha157]

=if(Match(LastReportedBucket, '[1 DAY]','[2-7 DAYS]'),'[1-7 DAYS]',

if(Match(LastReportedBucket, '[8-30 DAYS]','[31+ DAYS]'), '[7+ DAYS]',

if(Match(LastReportedBucket,'NEVER'),'NEVER',LastReportedBucket

)

))

[wdchristensen]

It is much easier to code solution when actual sample data is provided... I agree that you don't need brackets unless they are present in your actual data.

[wdchristensen · Accepted solution]

I like the way Neelam coded his "no match condition" because I am not typically the data owner and in my experience data will commonly change without anyone notifying me. So in 6 months when someone adds a new category '0 SAME DAY', I don't want those records falling in the 'UNCATEGORIZED WARNING' category as I previously coded. In that scenario, any new category will require a code change. However, if you coded the last "if statement" with a reference back to the original category, you might get lucky and not have to touch the code. In that example, if they wanted same day to be in it's own category then no change is needed.

=if(LastReportedBucket='1 DAY' OR LastReportedBucket='2-7 DAYS', '1-7 DAYS',

     if(LastReportedBucket='8-30 DAYS' OR LastReportedBucket='31+ DAYS', '7+ DAYS',

          if(LastReportedBucket='NEVER', 'NEVER', LastReportedBucket)))

[AC]

Thanks William, this works fine and it is working with IF clause not with if match

I have another field called ReportingBucket and the the values are 1 DAY, 2 DAYS, 3 DAYS.....30 DAYS from this i was trying to display first 7 DAYS (1 DAY, 2 DAYS...7 DAYS) and am using like below

=if(match(Onboarad.ReportingBucket,'1 DAY','2 DAYS','3 DAYS','4 DAYS','5 DAYS','6 DAYS','7 DAYS'),Onboarad.ReportingBucket)

[wdchristensen]

What I think you are wanting to do is check to see if the ReportingBucket is between 1 DAY and 7 DAYS. However you can't just do a numerical check but you have a field that contains string values. If we removed " DAY", " DAYS" and "NEVER" we could cast the remaining characters to an integer. So 7 DAYS would return the number 7 with the formula below:

Num(Replace(Replace(ReportingBucket,' DAYS',''),' DAY',''), '0')

So you could use the test condition 1-7 as follows with an if statement.

IF(Num(Replace(Replace(Replace(ReportingBucket,'NEVER’, '-1'),' DAYS',''),' DAY',''), '0') > 0

AND Num(Replace(Replace(Replace(ReportingBucket,'NEVER’, '-1'),' DAYS',''),' DAY',''), '0') <= 7, '1-7 DAYS', 'NOT 1-7 DAYS')

As a personal preference I don't like these types my formulas in the application. I typically do this transformation in my load scripts so if I need to have a numeric field for my IF statement, I just create a new field but it will work either way.

[AC]

Hello William, Its not working by your formula

[wdchristensen]

Try:

IF(Num (Replace (Replace (Replace (ReportingBucket,'NEVER', '-1'),' DAYS',''),' DAY',''), '0') > 0

AND Num(Replace(Replace(Replace(ReportingBucket,'NEVER', '-1'),' DAYS',''),' DAY',''), '0') <= 7, '1-7 DAYS', 'NOT 1-7 DAYS')

Some of my single quotes were from MS Word and that can cause issues...

If the above formula doesn't work please let me know exactly what error / response you get. Does the logical process make sense to you (converting string to numeric)? Thanks