Split data for specific cells only

[#1]

Hello,

I have the following data:

Title	DwnID	Type	Downloads
Title1	1	same	20
Title2	1	same	0
Title3	2	same	40
Title4	2	same	0
Title5	3	dif	60
Title6	4	dif	80

and I want to split the Downloads for titles which have the same DwnID:

Title	DwnID	Type	Downloads
Title1	1	same	10
Title2	1	same	10
Title3	2	same	20
Title4	2	same	20
Title5	3	dif	60
Title6	4	dif	80

If I use Sum({<Type={'same'}>} Downloads/2) I get the right values for Titles which already have a Downloads value but for the rest the value stays '0'.

Any suggestions?

Kind Regards,

Ivelin

[Gysbert_Wassenaar]

You can calculate it with avg(total <DwID> Downloads). See attached example.

[#3]

Hey thanks Gysbert but it doesn't really work in my case.

Downloads/2 was also only one of the cases I have, for others the calculation should be Downloads*30%, for example.

[sujeetsingh]

Why dont you give sample

[#5]

Title1 and Title2 have 20 and 0 in downloads according to the first table and in reality they both have 10 downloads each. That's because these 2 titles share the same DwnID. On the other side, Titles 5 and Title6 have different DwnIDs and thus the values of 60 and 80 are the correct.

Title	DwnID	Type	Downloads
Title1	1	same	=20*50%
Title2	1	same	=20*50%
Title3	2	same	=40*50%
Title4	2	same	=40*50%
Title5	3	dif	60
Title6	4	dif	80

[#6]

Here it is

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[#8]

That's what I'm looking for, thanks!

[#9]

Hi Gysbert,

I encountered an issue with this solution - selecting a Title returns its original value instead. Is there another way around it and/or is it possible/better to do it in the Script?

[Gysbert_Wassenaar]

You could try this:

sum({1<DwnID=p(DwnID)>} total <DwnID> Downloads )/count({1<DwnID=p(DwnID)>} total <DwnID> DISTINCT Title)

If you select a title you get the list of titles with the same DwID as the selected title.

But it's probably better done in the script. Maybe adding this will do the trick:

join load sum(Downloads)/count(distinct Title) as SplitDwnl, DwnID

resident Table1 group by DwnID;