topic YTD by month in App Development

YTD by month

Blunck-TotalFitouts — Fri, 02 Oct 2020 05:44:06 GMT

Hi all,

Within a pivot chart (see screen shot attached) I am using the following expression to show a conversion % per month for the 2020/2021 FY:

Count({$<[QuoteData.QuoteSiteConversion]={'Yes'},QuoteData.FYear={'2020/2021'}>} distinct [QuoteData.Quote])
/
count({$<QuoteData.FYear={'2020/2021'}>}distinct [QuoteData.Quote])

This works perfectly.

I now need to create a new expression, that shows the YTD conversion % as the months continue into the 2020/2021 FY. Tried the following but not getting any luck.

RangeCount(Above(Total Count({$<[QuoteData.QuoteSiteConversion]={'Yes'},[QuoteData.FYear]={'2020/2021'}>} distinct [QuoteData.Quote]), 0, [QuoteData.fMonth]))
/
RangeCount(Above(Total Count({$<[QuoteData.FYear]={'2020/2021'}>} distinct [QuoteData.Quote]), 0, [QuoteData.fMonth]))

Would appreciate some help with this!

Cheers

Carl

Re: YTD by month

Anil_Babu_Samineni — Fri, 02 Oct 2020 06:34:50 GMT

@Blunck-TotalFitouts Instead of RangeCount, You can use RangeSum like

RangeSum(Above(Count({$<[QuoteData.QuoteSiteConversion]={'Yes'},QuoteData.FYear={'2020/2021'}>} distinct [QuoteData.Quote])
/
count({$<QuoteData.FYear={'2020/2021'}>}distinct [QuoteData.Quote]), 0,Rowno(TOTAL)))

Re: YTD by month

talebmakrani — Sat, 03 Oct 2020 14:28:49 GMT

Use “before “instead of “above “

Re: YTD by month

Blunck-TotalFitouts — Tue, 06 Oct 2020 10:22:05 GMT

Thanks both, this worked in the end 🙂

(RangeSum(Before(Total Count({$<[QuoteData.QuoteSiteConversion]={'Yes'},[QuoteData.FYear]={'2020/2021'}>} distinct [QuoteData.Quote]), 0, [QuoteData.fMonth])))
/
(RangeSum(Before(Total Count({$<[QuoteData.FYear]={'2020/2021'}>} distinct [QuoteData.Quote]), 0, [QuoteData.fMonth])))