YTD by month

[Blunck-TotalFitouts]

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

[Anil_Babu_Samineni]

@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)))

[talebmakrani]

Use “before “instead of “above “

[Blunck-TotalFitouts · Accepted solution]

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])))