Calculations in output of a BI query

[Former Member]

Hi,

We use a BI query in our model which gives us an overview of activity status within a campaign. So, we give a campaign_id as variable and we receive a list of status with the number of activities that have that status.

For example:

We give campaign_id=TEST as variable and receive this list:

Status  No. of activities
Open    4
Closed  32

Question 1: How can I make some sort of calculation of the total number of activities(so like sum up the cloumn No. of activities in the output table?

Question 2: How can make the following calculation

(No_of_activities_with_status_open / No_of_activities_total) * 100%

?

So actually I want to calculate the percentage of a certain status and display that in a table. Any suggestions?

thanks in advance and best regards,

Jan Laros

[Former Member · Accepted solution]

Hi Jan Laros,

Try the below steps.

1. Drag one 'Aggregate Data' operator from output table.

2. In 'Configure Element window' of the aggregate data select the value as 'SUM' for 'No. of activities' field

3. Drag a form from it and it shows the total sum(total no of activities) of the field.

This is the solution for your first question.

For your second question do the required calculation in BI query itself and display it Visual Composer.

Regards

Basheer

Edited by: Basheer on Feb 25, 2009 11:15 AM

[Former Member · Accepted solution]

Thanks Basheer. I can't drag an aggregate operator from the table though, only directly from the data service. And when I do that, I can't choose "SUM", only "COUNT", "MIN", "MAX".

[Former Member · Accepted solution]

'SUM' is only available for numeric fields not for text fields.

Regards

Basheer

[Former Member · Accepted solution]

Edit: Update: We have changed the query and now I have the result taht I wanted:

Activities    StatusISNOTcanceled    Rate
        100                      50                 50%
        100                      50                 50%

So, next questions. I only want one line/row in the table. Why am I getting two and how can I change that?

Thanks for your help this far Basheer! Points awarded.

Jan

Edited by: Jan Laros on Feb 25, 2009 4:34 PM

[Former Member · Accepted solution]

Hi Jan,

If you get repeated rows, the problem is from your BI query. Check your BI query. If it is correct set 'No of rows' property of table as 1.

Regards

Basheer

[Former Member · Accepted solution]

Ok, thanks Basheer. I can not change the no. of rows property in the table though. This is greyed out, but I'll check with the BI guys first that the query is correct.