How to Create a bar chart with the result of a keyword search

[#1]

i have a Comments column. Need to report on the count of the particular keyword used.

Eg,

Ordered date Product ID Comment

1/1/2009 BRD123 Product returned due to defect

1/1/2009 BRD234 Pricematch given for the product

1/1/2009 BRD455 Customer not satisfied with the product

1/1/2009 BRD123 Product returned due to defect

i need to report on the number of times word "Defect" is used in the comments column and also the percentage of the word defect in comparision to the total comments.

[deepakk]

hi,

Use the below expression

Count( if(wildmatch(Comment,'*defect*') >0,ProductID))

[#3]

Hi Deepak,

i am getting invalid expression error .

Could you please help me.

[pover]

Deepak solutions looks good. If ProductID has a space remember to use [Product ID].

You can also get the same result with

count({$<Comment={"*defect*"}>} Comment)

and divide it by the total number of comments

count(Comment) that might need a total modifier depending on the report you want to produce:

count(total Comment)

Regards.

[#5]

Thanks Deepak and Karl,

I Created a separate field using

if( wildmatch(comment,'*defect*') >0,ProductId) as "New product id" ,

and then doing a count(Productid) on the chart and it is working.

Do you see any issues with the above approach.

Also is it possible to send the keyword 'Defect' dynamically.

For example today i want to analyse on keyword defect and tommorrow it could be return ,refund or something else

[pover]

I assume you are doing count([New product id]) and not count(ProductId), correct? This would be better to do in the graphic side instead of the script side so that you can change it as you said without waiting for it to reload.

Also, to give power to the user in the graphical interface, modify the formula that I gave you to:

count({$<Comment={"*$(vKeyword)*"}>} Comment)

where vKeyword is a variable that the user can modify in an inputbox.

Or if you have a listbox for the field Comment, the user can filter the comment as they wish and will still be able to divide the count of the comments selected by a total number of comments that you calculate by ignoring the selection. For example,

count(Comment)
/
count({$<Comment=>} Comment)

Regards.

[#7]

Can i use the formula for supplying mutiple keywords.

Eg, can i report on defect, refund, return on a single chart

[pover]

You can do that with the second solution as is and I think that is the best solution, but the first formula would need some modification and a little help for the user.

The formula would be

count({$<Comment={"=Comment=wildmatch(Comment,$(vKeyword))"}>} Comment)

and in the inputbox the user would have to put

'*defect*','*result*','*return*'

Regards.

[#9]

Hi Karl,

i used your first solution

count({$<Comment={"*$(vKeyword)*"}>} Comment) . But it is only displaying one line chart at a time.

i want to display one line chart for each of the keyword supplied.

Could you please help me with the same.

I have ordered date as the dimension and the above formula in the expression window.

[pover]

You could create more variables and assign a different keyword in each variable and use an expression for each variable:

count({$<Comment={"*$(vKeyword_1)*"}>} Comment)

count({$<Comment={"*$(vKeyword_2)*"}>} Comment)

count({$<Comment={"*$(vKeyword_3)*"}>} Comment)

etc.

Regards.