Finding maximum value - Cumulative Calculation

[EliGohar]

Hi All,

I'm having this table with the following data:

The table presents data of Entering vs. Exiting people by date and hour.

I want to create KPI that finds the max value for a specific date from my cumulative expression (in the above example, the max is 403).

I tried this expression:

=max(aggr(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))- 
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())),Date,[Hour (hh)]))

But I got wrong value.

Please assist me with the correct expression.

Thanks,

Eli.

[Anil_Babu_Samineni]

IS this your actual expression for above cumulative numbers?

rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))- 
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno()))

If so, I would try in this way? Here, I am doing set analysis filters for Max as well.

=max({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} aggr(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))- 
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())),Date,[Hour (hh)]))

[EliGohar]

@Anil_Babu_Samineni Thanks for your help, for some reason the expression you provided gave me 394.

No idea where this number came from.

Any idea?

[Anil_Babu_Samineni]

Can you check what no is coming on this?

If(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno()))==max(TOTAL aggr(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())),Date,[Hour (hh)])), rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())))

[EliGohar]

@Anil_Babu_Samineni It's not valid:

Error in expression: ')' expected

[Anil_Babu_Samineni]

My bad, I see some extra string in the above. Try this? I removed equal symbol

If(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno()))=max(TOTAL aggr(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())),Date,[Hour (hh)])), rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())))

If the above one not working, First check this part what it is returning?

max(TOTAL aggr(rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Entering'}>} ObjectID),0,rowno()))-
rangesum( above( count({<[Class Category ID]={0}, [Path & Area]={'Exiting'}>} ObjectID),0,rowno())),Date,[Hour (hh)]))

[EliGohar]

@Anil_Babu_Samineni above expression returns null.

The second returns 394.

Thanks.

[Anil_Babu_Samineni]

Rather back to back, will you share sample to check? I would recommend QVW or QVF this case (Demonstrated application only when you feel data is secure). You can share scramble data to test.

[EliGohar]

@Anil_Babu_Samineni 

Attached is the data in an Excel file, Hope it helps.

[Ksrinivasan]

hi,

try this link,

you will have some idea to overcome

https://community.qlik.com/t5/New-to-Qlik-Sense/Peek-Value-Carry-max-value-of-previously-read-record...

ksrinivasan.