Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kgraham25
New Contributor II

Aggr function for weighted data

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:

InterviewIDDatasetCountryRevenueWeighting factor
1GlobalUSLow (not dependent)1.5
2GlobalUSHigh (not dependent)1.5
1

World

USLow1.25
2WorldUSHigh1.75
3GlobalCanadaHigh (not dependent)1.1
3WorldCanadaHigh1.6
4GlobalCanadaLow (not dependent)1.1
4WorldCanadaLow1.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

Tags (2)
4 Replies
Partner
Partner

Re: Aggr function for weighted data

where is your "Response" field?

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

kgraham25
New Contributor II

Re: Aggr function for weighted data

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

Re: Aggr function for weighted data

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
New Contributor II

Re: Aggr function for weighted data

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