Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to count the number of vendors that meet the following conditions.
1. Recent 1 year. 2. The total Amount > = $(V1) < = $(V2)
I don't know how to write the expression "SUM" into the expression "Count".
sum({<T3.DateC= {">=$(=AddMonths(Max(T3.DateC), -11))"}>}[T3.Amount]) >=$(V1)<=$(V2)
Count({< ?????? >}distinct[T3.VendorID])
I tried the following methods, but the results were incorrect because Amount was not aggregated.
Count({<
[T3.Amount]={">=$(V1)<=$(V2)"},
T3.DateC= {">=$(=AddMonths(Max(T3.DateC), -11))"}
>}distinct[T3.VendorID])
Not sure I understand what you are trying to do.... would you be able to share a sample and provide the output you are hoping to see from it?
Count(distinct Aggr(if(sum({<T3.DateC= {">=$(=AddMonths(Max(T3.DateC), -11))"}>}[T3.Amount]) >=$(V1)
and sum({<T3.DateC= {">=$(=AddMonths(Max(T3.DateC), -11))"}>}[T3.Amount]) <=$(V2) ,[T3.VendorID])
, [T3.VendorID]))