Average of total dimension vs selected record

[imtiaz_ullah]

Hi guys,

I am trying to calculate the avg of a value within a dimension so I can visualise to outputs.

actual sum of current selection vs AVG of the entire dimension

Background.

I am trying to calculate the percentage of emails opened and then compare it against the avg number of emails opened within a particular dimension.

By this I mean that I have a one to many relationship - a list contains many mailings. List is a dimension within my data model.

Therefore I want to calculate the avg no. Opens for the selected list vs the actual select mailing value.

Regards

Immy

[imtiaz_ullah]

Just to add... the Avg calculation should not update if I then select a mailing record, as it should reflect the avg of the previous dimension.

I hope that makes sense?

[MK_QSL]

It's difficult to understand/work on your question without any sample data or sample application.

[imtiaz_ullah]

Yes i understand but I need to be careful about uploading the data as some of it is sensitive.

Thoughts?

[MK_QSL]

You can always create few lines of dummy data and upload by excel...

[imtiaz_ullah]

OK so just a data sample is enough and the qvf or just excel?

[MK_QSL]

Excel data along with your required result would be more than enough

[imtiaz_ullah]

Data attached.

So if you take for example the list = TestData_abandoned_basket, this will have a number of mailings which I also have as a dimension. Hierarchy is List > Mailing.

The average number of TotalClicks for all mailings in the list on 01/12/2014 is 12, but the actual number of TotalClicks for the mailing named 00824_Trigger_AdandonedBasket is 5.

I want to display in a textbox/KPI the average number for the selected list.

If I select a mailing after that, then the average number should remain for the list and not change once I click on the mailing.

Cheers

Immy

[MK_QSL]

=Avg({<Mailing = ,  list_ = {'TestData_abandoned_basket'}>}TotalClicks)

[imtiaz_ullah]

Thanks for the correct answer Manish:

Avg({<MailingName = >}TotalClicks)