Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi my friends, i have the next set analysis in my table
Presupuesto:
=((Sum({<[ACCNT_CODE]={"611110", "635110", "635120", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))
Real:
=(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))))
Diferencia:
=(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))
But i just want to show only positive values, help me please...
@JASalinas , you can take your entire formula, ask if it is positivo, show it again :
Diferencia:
if((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])) >0,
(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))
)
@JASalinas , you can take your entire formula, ask if it is positivo, show it again :
Diferencia:
if((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])) >0,
(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))
)
Thank you so much my friend, now if you allow me to ask you other question, How can i to have a counter only positve values?... Thanks in advance!...
Debería ser algo así :
count(aggr((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])), CampoNivelSuperior))
donde CampoNivelSuperior, será un campo que permita agrupar los valores, para luego contarlos.
ok, mira ya lo puse en un KPI y el resultado es cero, pero yo quisiera que solo contara los que estan en verde, Verde 16, rojos 2, porque la suma del counter es 18, la condicionante seria que sean mayores de 0 que cuente los que estan en verde y viceversa...
Gracias @QFabian por tu ayuda y por tu respuesta... saludos!...
Claro, entonces toca poner toda la formula con el if, para que los cuente con el aggr
Mira hice esto:
count(Aggr(if((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])) >0,
(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))), distinct FNCT))
pero me sigue dando cero...
talvez con unos datos de prueba podriamos revisar en detalle
Amigo si quieres nos ponemos de acuerdo, y entras a mi compu con teamvier gracias nuevamente por tu ayuda, +52 9993221163 estoy en México... y podemos probar... tu me dices perdón si estoy abusando de tu ayuda... y si das cursos estoy interesado también jejeje....