Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2014
10:35 AM

06-13-2014
10:35 AM

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