Weighted Average Set Analysis With Aggr in a Text Box

[owais_sarwar]

Hi All,

I need to show the weighted average copy duration in a text box basis share of spend a brand has basis its  ad durations. The sample table is attached along is self explanatory which contains the steps to reach to a desired number in a text box. Please help me out how can i get this done.

I have reached to the point where i am getting the product of 'share of spend'  and duration in a table. Whereas, this has to be done in a text object. Below is the set analysis i have reached up till now.

Num(Num(Sum({$}{<Flag_Key = {"GRP"}>}[Spend]),Brand,Duration)/Num(Sum(Total <Brand>{$<Flag_Key = {"GRP"}>}[Spend]))*Duration)

Thanks in Advance,

[RUNIL]

Try with below. Showing in text box use Aggr on dimension.

Aggr(Num(Num(Sum({$}{<Flag_Key = {"GRP"}>}[Spend]),Brand,Duration)/Num(Sum(Total <Brand>{$<Flag_Key = {"GRP"}>}[Spend]))*Duration),GRP)

[owais_sarwar]

Hi Runil,

Thanks for your reply. I tried your set analysis, it didn't work. Just for your knowledge, 'GRP' is basically the 'flag key' for a data source.

[RUNIL]

Use your dimension instead of GRP. I think your dimension is Brand. It should come in Text box using by the Aggr.

[owais_sarwar]

Yeah i did try it (by replacing GRP with Brand) as this Indeed is the dimension. Unfortunately not working.

[Kushal_Chawda]

can you try below

=sum(aggr((Sum({<Flag_Key = {"GRP"}>}[Spend])/Sum(total <Brand>{<Flag_Key = {"GRP"}>}[Spend]))*Duration,Brand,Duration))

[owais_sarwar]

Thank you for your help.. Still not done. Its is just showing 0 as an output. Can you please try it yourself by making a sample table in the same scenario in Qliksense or Qlikview whichever you have?