Dynamic AI Support Forum

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

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

when I try this I get an error message

see attached

Hi W,

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

Thanks

Bo

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

It worked!

Thank you so much for your help

Regards

Wayne