Aggr function for weighted data

[kgraham25]

Hi

I am weighting my data and my weighting factors are dependent on both Country and Dataset for the most part. However, within one dataset, the weighting is also affected by revenue. A record could also sit within more than one Dataset.

Example:

InterviewID	Dataset	Country	Revenue	Weighting factor
1	Global	US	Low (not dependent)	1.5
2	Global	US	High (not dependent)	1.5
1	World	US	Low	1.25
2	World	US	High	1.75
3	Global	Canada	High (not dependent)	1.1
3	World	Canada	High	1.6
4	Global	Canada	Low (not dependent)	1.1
4	World	Canada	Low	1.3

For my analysis, I want to do a weighted count of distinct interviewIDs after being forced to select a Dataset from a filter pane. The selection of either Global or World will determine which weighting factors should be used.

I had originally been working on the Global weighting only (no Revenue dependence) and the following AGGR function was working for me:

SUM(AGGR(

          COUNT (DISTINCT InterviewID)

         *IF(LEN(TRIM([Weighting factor]))=0,1,[Weighting factor]),

         Response,Country,Dataset

))

Now that I've come to include the World dataset and revenue, I can't get it to work. I have tried adding revenue into the function as shown below but with no luck:

SUM(AGGR(

COUNT (DISTINCT InterviewID)

    *IF(LEN(TRIM([Weighting factor]))=0,1,[Weighting factor]),

    Response,Country,Dataset,Revenue

))

I would be grateful of any help!

Thanks!

KG

[agigliotti]

where is your "Response" field?

and also what are dimensions and measures are you using in table object?

[kgraham25]

Hi Andrea

My Response field could be a response to any question in my questionnaire.

The table isn't something I'm using in my analysis - it was just to demonstrate how the weighting is affected by country, dataset and revenue for context.

I will be working out the counts to responses of a particular question but then I need to weight it.

Hope that's more clear?

Thanks!

KG

[sunny_talwar]

What exactly goes wrong when you use this?

SUM(AGGR(

COUNT (DISTINCT InterviewID)

    *IF(LEN(TRIM([Weighting factor]))=0,1,[Weighting factor]),

    Response,Country,Dataset,Revenue

))

Would you be able to let us know what is the expected output based on the sample data you have provided above?

[kgraham25]

Hi both

Thanks for taking the time to look at this. As it turns out, my weighting factor was set up incorrectly and has now been corrected.

Thanks!

KG