Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can calculate it with avg(total <DwID> Downloads). See attached example.
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.
Why dont you give sample
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 |
Here it is
See attached qvw
That's what I'm looking for, thanks!
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?
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;