Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
Hello everyone, I need your help, I have to count the last recorded management for each client, but I do not know how to do it, can you help me? The management that must be counted is the one with the highest Num_Gestion value, but I am using a variable because the client can select any range of dates to obtain the report. Please, your help, I've been trying to solve it for 3 days. Thank you very much
count
( Distinct
{
(
(<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=>} Num_Cedula_Cliente)>
*
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>
)
)
}
Num_Cedula_Cliente
)
Thanks for your help.
I had to change the dimension by:
Aggr(FirstSortedValue(Descripcion_Gestion, -Num_Gestion), Num_Cedula_Cliente)
And the measure by:
Sum(Aggr(
count(distinct
{(
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>
*
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>
)}
Num_Cedula_Cliente)
*
Count(Aggr(FirstSortedValue(Descripcion_Gestion, -Num_Gestion), Num_Cedula_Cliente))
, Proyecto, Descripcion_Gestion, Num_Cedula_Cliente))
Now I have what I wanted.
Hi Karen
I'm thinking you want to add to your set analysis something about Num_Gestion = Max(Num_Gestion ) you'll need to dig a little to get the syntax right.
as a side note it might be worth adding to your post to explain the things you have tried to get a solution.
Ron
Thanks for your answer.
I've been trying the following, but none has worked:
{
(
(<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=, Num_Gestion={$(vGestion)}>} Num_Cedula_Cliente)>
*
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=, Num_Gestion= {$(vGestion)}>}Num_Cedula_Cliente)>)
)}
Num_Cedula_Cliente
), Proyecto, Descripcion_Gestion))
{
(
(<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=>} Num_Cedula_Cliente)>
*
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>)
)}
Num_Cedula_Cliente
), Proyecto, Descripcion_Gestion))
What am I doing wrong?
Hi Karen
What is the definition of vGestion?
For troubleshooting try to calculate the highest Num_Gestion per Proyecto, Descripcion_Gestion
Also you may need to include the field Proyecto in your table. Aggr needs the field present to be able to group by it, I think. Give it a try.
I'll dummy something up and see if I can get it to work.
Ron
Hi Ron,
Sorry, i forgot to write the definition that is Max(Num_Gestion), without = because it doesn't work with =.
Thanks for your suggestion. I will add the field Proyecto, i hope it works.
Please, tell me if you find a good solution.
Hi Karen
I've made something that works in my system for calculating the sum of a field given another field is the max value:
Sum({<[HS Price]={">=$(=Max(total<[HS Supplier Name] >[HS Price]))"},
[HS Supplier Part Number Original]=P([HS Supplier Part Number Original])>}total<[HS Supplier Name]> PO_OrderCount)
I think the bit you have missed is use of TOTAL<> where you put the per field names within the <>'s
May be, modify your expression like this:
=Count(Aggr(Count({<Num_Gestion = {'$(=vGestion)'}>}Num_Gestion),Num_Cedula_Cliente))
Sum(Aggr(count ( distinct | ||
{ | ||
( | ||
(<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=, Num_Gestion={'$(=vGestion)'}>} | Num_Cedula_Cliente)> | |
* | ||
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=, Num_Gestion={'$(=vGestion)'}>}Num_Cedula_Cliente)>) | ||
)} | ||
Num_Cedula_Cliente | ||
), Proyecto, Descripcion_Gestion)) |
Hi,
Thanks for your answer. I tried it, but it still doesn't work.
Hi Ron,
So sorry, I think I didn't understand you. Your suggest is this:
Sum(Aggr(count ( distinct {<Num_Gestion={$(vGestion)}>}
{
(
(<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=, total Num_Gestion>}Num_Cedula_Cliente)>
*
<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=, total Num_Gestion>}Num_Cedula_Cliente)>)
)}
Num_Cedula_Cliente
), Proyecto, Descripcion_Gestion))
I've changed vGestion by Max(total Num_Gestion)
Please, correct me!