How to select one value from the output array of aggr() based on dimension_id...?

[#1]

Hi Friends,

We have data which is as follow

I want to add "affinity param count" for each distinct affinity id grouped by Market, Brand, Target_group and Calendar_id

After this I want to divide each sum (affinity param count) with the sum of total population of all 4 markets

After this, I want to multiply each result with a constant that varies with each affinity_id

Then I want to SUM it...

For example...For Brand = 1, Target Group =1, Calendar_id = 201401

I want to add affinity param count 13729.76 +3225.94+4683.9+3352.29 (affinity_id = 1)....call it A

Similarly I want to add total population for affinity_id =1...Call it B

Now Divide A/B....call it X1

I want to repeat same for each affinity_id = 1 to 5 so that I get X2, X3, X4 and X5

I want to multiply X1 with 0.06, X2 with 0.55, X3 with 1.1, X4 with 3.6 and X5 with 5

Then I want to add all these = (0.06 * X1) + (0.55 * X2) + (1.1 * X3) + (3.6 * X4) + (5 * X5)

I want output in text box...

So far we have done this -

concat( Aggr((sum(affinity_param_count)/sum(total_population_affinity)),affinity_id), ' / ')

This is giving me X1 / X2 / X3 / X4/ X5

Dont know how to proceed further  which is multiplying each X with constant and then adding it up

Please Help ASAP

Thanks

Venugopal G

[Kushal_Chawda · Accepted solution]

You can do the below set up

1) First create the 5 Variables for each affinity ID on Front End

vAffinity1 =sum({<affinityID={'1'}>}affinity_param_count)/sum({<affinityID={'1'}>}total_population_affinity)

vAffinity2 =sum({<affinityID={'2'}>}affinity_param_count)/sum({<affinityID={'2'}>}total_population_affinity)

vAffinity3 =sum({<affinityID={'3}>}affinity_param_count)/sum({<affinityID={'3'}>}total_population_affinity)

vAffinity4 =sum({<affinityID={'4'}>}affinity_param_count)/sum({<affinityID={'4'}>}total_population_affinity)

vAffinity5 =sum({<affinityID={'5'}>}affinity_param_count)/sum({<affinityID={'5}>}total_population_affinity)

2)  Now  do the selection  of Brand = 1, Target Group =1, Calendar_id = 201401 on front end

3) Create the text object and put the below expression

    (0.06 * $(vAffinity1)) + (0.55 * $(vAffinity2)) + (1.1 * $(vAffinity3)) + (3.6 * $(vAffinity4)) + (5 * $(vAffinity5))

[Gysbert_Wassenaar]

Perhaps like this:

helper variable:

vFactor: pick(affinity_id, 0.06, 0.55, 1.1, 3.6, 5)

text box expression:

=sum(Aggr($(vFactor)*sum(affinity_param_count)/sum(total_population_affinity),affinity_id))

[#4]

Hi Kush,

Thank's for your reply....

It's working.....:)

Regards,

Venugopal G