Completion Rate formula

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Completion Rate formula

Postby WStansbury » Wed Jan 25, 2012 9:52 pm

In the table below, the column headings of '0' and '1' is contained in the field name of SHOWED FLAG where '0' = No and '1' = Yes

I want to calculate a formula name 'Completion Rate' where it would be '1'/'n' * 100

I tried [[0]]/[[1]]*100. This did not work but I am sure I need to add the SHOWED FLAG field in the formaula but don't know how.

STF_NAME 0 1 n
Andre Chillers 35 42 77
Belinda Gonzales 12 33 45
Carolyn Gonzales 84 67 151
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am

Re: Completion Rate formula

Postby Eddy Kleinjan » Wed Jan 25, 2012 11:05 pm

Hi W,

My guess is that in your report you put a column group on SHOWED FLAG and do the calculation that way. Unfortunately this is not going to work with a _column_ group in place. There is an alternative way of doing this by creating two SQL formula fields in the report (or your data source if you like) and have those displayed as totals, instead of a group by SHOWED FLAG. That way you _can_ do your calculation.

The SQL formula's could look like this (SQL Server syntax, you might need a different syntax):
NAME: ShowedFlag0
FORMULA: CASE WHEN [SHOWED FLAG] = '0' THEN [Value] ELSE NULL END

NAME: ShowedFlag1
FORMULA: CASE WHEN [SHOWED FLAG] = '1' THEN [Value] ELSE NULL END

Now in your report you group by the name and have ShowedFlag0 and ShowedFlag1 as totals. Then in Dynamic, you can add the Total formula like this:
[[D_ShowedFlag0_D]]/[[D_ShowedFlagN_1]]*100

Please let us know how this works for you.

Best regards,
Eddy Kleinjan
User avatar
Eddy Kleinjan
Certified Dynamic AI Professional
 
Posts: 29
Joined: Thu Sep 13, 2007 12:10 pm
Location: Hengelo, Netherlands

Re: Completion Rate formula

Postby WStansbury » Thu Jan 26, 2012 1:36 am

when I try this I get an error message

see attached
Attachments
apptbyPI.xlsx
(205.67 KiB) Downloaded 1682 times
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am

Re: Completion Rate formula

Postby admin » Thu Jan 26, 2012 10:21 am

Hi W,

I don't see the error message in the excel sheet - can you repost just the error text?

Thanks

Bo
User avatar
admin
Certified Dynamic AI Professional
 
Posts: 177
Joined: Thu Jan 01, 1970 1:00 am
Location: Copenhagen, Denmark

Re: Completion Rate formula

Postby Eddy Kleinjan » Thu Jan 26, 2012 11:13 am

Looking at the screenshots you sent, I think you should change the column names. The column names that I gave are based on the data source that I used, but you should of course use the ones that come from your data source.

It seems that the column name that holds the flag is called [SHOWED_FLAG], note the underscore. Also in your screenshot, the formula is of the type 'Text (SQL)', but it should be 'INTEGER (SQL)' as the result is used for counting.

Also note that in my example, I'm using the column [Value] to deliver the value from the CASE statement. I don't know what your report looks like, whether you are counting rows for totalling or are adding up a total, but you should change that also in your case statement. If you are counting rows, you could change it to something like this: CASE WHEN [SHOWED_FLAG] = '0' THEN 1 ELSE 0 END.

After you made those changes and both are totaling the values of the formula for '1' and for '0', then you should remove the group on the [SHOWED_FLAG] column as it is no longer needed.

Then you can create the resulting formula like this:
[[D_ShowedFlag0_D]]/[[D_ShowedFlag1_D]]*100

Hope this helps, please let us know.

Best regards,
Eddy
User avatar
Eddy Kleinjan
Certified Dynamic AI Professional
 
Posts: 29
Joined: Thu Sep 13, 2007 12:10 pm
Location: Hengelo, Netherlands

Re: Completion Rate formula

Postby WStansbury » Thu Jan 26, 2012 5:59 pm

It worked!

Thank you so much for your help

Regards

Wayne
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am


Return to Technical Support

cron