topic Re: count based on latest date in App Development

count based on latest date

Zahmed14 — Sat, 16 Nov 2024 17:36:34 GMT

Hello guys ,

i need to make a count ID and by status based on Max date

Also how i get the status for the latest date
Maxdate is expression i wrote : =aggr( max(UDATED),OBJNR) . how to put that count in charts

Re: count based on latest date

Ksrinivasan — Sat, 16 Jan 2021 20:10:29 GMT

hi,

you can use Straight table,

DIMENSIONS are 1. MAXDATE, 2. STATUS

MEASURES is 1. Count(ID)

ksrinivasan

Re: count based on latest date

Zahmed14 — Sun, 17 Jan 2021 07:18:42 GMT

how can i put this count in chart or KPI componant counting by status type

How to get the latest status for every ID

Re: count based on latest date

Ksrinivasan — Sun, 17 Jan 2021 07:50:29 GMT

hi,

1. you have to create variable to get Latest Date as below

2. we will Call the Variable in in pie chart

1. DIM=STATUS

2. MEASURE=count({$<MAXDATE = {'$(=vMaxdate)'}>}COUNT_ID)

result as below:

ksrinivasan.

Re: count based on latest date

Zahmed14 — Sun, 17 Jan 2021 09:03:05 GMT

thats good but " MAXDATE " was experssion in table i wrote ... so it gives me error when i write it in the variable of VMax_date

Re: count based on latest date

Ksrinivasan — Sun, 17 Jan 2021 09:26:15 GMT

hi, no need to maintain separate MAXDATE expression for table.

you can use same Variable: vMaxdate =MAX(MAXDATE) for both Table and Chart as Expression and DIM also.

as below

ksrinivasan.

Re: count based on latest date

Zahmed14 — Sun, 17 Jan 2021 09:39:27 GMT

I mean the MAXDATE in blue circle

Re: count based on latest date

Ksrinivasan — Sun, 17 Jan 2021 09:54:05 GMT

hi,

what you have marked blue box value is field name, its from your table last field.

its not considered here as dimension , expression and formula.

ksrinivasan.

Re: count based on latest date

Zahmed14 — Sun, 17 Jan 2021 09:57:24 GMT

No thats what am trying to say . the blue field is an expresion i wrote in my table not a field name coming from source i just wore this exp in table "=aggr( max(UDATED),OBJNR) " to get the blue field

so it will not appear when i create my variable

Re: count based on latest date

Zahmed14 — Sun, 17 Jan 2021 11:02:13 GMT

how to get latest status for ID in chart or table ?

Re: count based on latest date

Ksrinivasan — Sun, 17 Jan 2021 11:13:26 GMT

hi,

below my dummy data from your table:

whether my Table and charts were met your requirement?

if, yes, please just follow my variable, DIM and Expressions.

ksrinivasan.

Re: count based on latest date

Ksrinivasan — Sun, 17 Jan 2021 14:09:34 GMT

hi,

sry

Now i have corrected my Dummy Data,

find the Latest Status for every ID as below in table:

which is having status in fifth column that is the latest ID and Status.

and you will get it by resident table from script itself.

revert as soon.

ksrinivasan.

Re: count based on latest date

Zahmed14 — Sun, 17 Jan 2021 19:15:24 GMT

I appology if i was not clear , the below is latest status for every ID here is example

i wrote the latest status exp as follow

latest status=If(aggr(nodistinct max({<Date_New={ "<=$(v_MAX)"} >}Date_New),OBJNR) = Date_New, TXT30)

Thanks KSRINIVASAN but if you Please advise if i can make add another condition on the latest status to get a specific status like active for example how can i add this in my function