Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
imtiaz_ullah
Creator
Creator

Average of total dimension vs selected record

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

9 Replies
imtiaz_ullah
Creator
Creator

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
MVP
MVP

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

imtiaz_ullah
Creator
Creator

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

Thoughts?

MK_QSL
MVP
MVP

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

imtiaz_ullah
Creator
Creator

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

MK_QSL
MVP
MVP

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

imtiaz_ullah
Creator
Creator

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
MVP
MVP

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

imtiaz_ullah
Creator
Creator

Thanks for the correct answer Manish:

Avg({<MailingName = >}TotalClicks)