Sum(Amount) Over (Partition by Month) having Amount over $25

Database
Enthusiast

Sum(Amount) Over (Partition by Month) having Amount over $25

Hello,

I am trying to calculate a percentage using the numerator and denominator below.

Numerator: Amount for the specific month if the Amount is > $25, if not 0

Denominator: Sum the Amounts for all the months having Amount>$25. The only catch is Summing only the amounts that are >$25. 

This will be the month level penetration percentage.

I have to calculate similar penetration for seasons and years. 

Any help to restrict the denominator to use only >$25 amounts is appreciated. 

Example:












Year Month Week Amount Numerator Denominator Percentage Numerator

Yearly
Denominator

Yearly
Percent Yearly
2014 1 1 10 0 217 0% 0 588 0%
2014 1 2 26 26 217 12% 26 588 4%
2014 1 3 89 89 217 41% 89 588 15%
2014 1 4 102 102 217 47% 102 588 17%
2014 2 1 7 0 134 0% 0 588 0%
2014 2 2 23 0 134 0% 0 588 0%
2014 2 3 56 56 134 42% 56 588 10%
2014 2 4 78 78 134 58% 78 588 13%
2014 2 5 22 0 134 0% 0 588 0%
2014 3 1 78 78 237 33% 78 588 13%
2014 3 2 37 37 237 16% 37 588 6%
2014 3 3 24 0 237 0% 0 588 0%
2014 3 4 122 122 237 51% 122 588 21%

Note that the Denominator doesn't sum any value that is not >$25. 

Thank you,

Sai

Tags (1)