Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
where is your "Response" field?
and also what are dimensions and measures are you using in table object?
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
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?
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