topic Re: Weighted Average Set Analysis With Aggr in a Text Box in App Development

Weighted Average Set Analysis With Aggr in a Text Box

owais_sarwar — Mon, 20 Dec 2021 21:38:40 GMT

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,

Re: Weighted Average Set Analysis With Aggr in a Text Box

RUNIL — Wed, 15 Jan 2020 07:44:46 GMT

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)

Re: Weighted Average Set Analysis With Aggr in a Text Box

owais_sarwar — Thu, 16 Jan 2020 07:02:36 GMT

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.

Re: Weighted Average Set Analysis With Aggr in a Text Box

RUNIL — Thu, 16 Jan 2020 07:48:11 GMT

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

Re: Weighted Average Set Analysis With Aggr in a Text Box

owais_sarwar — Thu, 16 Jan 2020 08:49:45 GMT

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

Re: Weighted Average Set Analysis With Aggr in a Text Box

Kushal_Chawda — Thu, 16 Jan 2020 11:12:43 GMT

can you try below

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

Re: Weighted Average Set Analysis With Aggr in a Text Box

owais_sarwar — Thu, 16 Jan 2020 12:25:24 GMT

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?