Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This is also not working.
Interesting thing is I just tried with some sample data and its working fine with simple If match but the same is not working in my actual application. Please see attached.
Below is the expression which I am using in my actual application and and it says expression is OK
and find the attached sample qvw as well.
!
Perhaps Onboard.LAST_PING_STATUS is returning a character ('1','2','3'...) and not an integer (1,2,3...). Maybe try:
=if(match(num(Onboard.LAST_PING_STATUS),'1 DAY','2 DAYS','3 DAYS','4 DAYS','5 DAYS','6 DAYS','7 DAYS'), Onboarad.ReportingBucket)
The expression looks correct and should work but if its not working for some reason then may be you can pull the numeric part of the Bucket and then instead of using 'match ' in calculated dimension, you can use NumnericBucketField is <=7 in the expression...just an idea!
This expression also says OK in expression editor but nothing is coming up in the table.
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')
The above expression gives the count for 1-7 DAYS and NOT 1-7 DAYS count , this is not what i am looking I want the counts for each individual day like
1 DAY,
2 DAYS,
3 DAYS.
........
7 DAYS
Maybe:
=if(match(num(Keepchar(Onboard.LAST_PING_STATUS, '0123456789')),'1 DAY','2 DAYS','3 DAYS','4 DAYS','5 DAYS','6 DAYS','7 DAYS'), Onboarad.ReportingBucket)
If make a table of the source data and the desired response and I can help with the formula.
Example:
Source (ReportingBucket) | Desired Result
1 | 1 Day
2 | 2 Days
NULL | NEVER
IF(Num(Onboard.LAST_PING_STATUS)=1, Text(Onboard.LAST_PING_STATUS) & ' Day',
IF(Num(Onboard.LAST_PING_STATUS)>1, Text(Onboard.LAST_PING_STATUS) & ' Days', Text(Onboard.LAST_PING_STATUS)))
William, still not able to find the solution for this.
I have created another thread for this issue, can you please check