topic Re: Date before for each row in App Development

Date before for each row

sfbi — Sat, 16 Nov 2024 17:56:24 GMT

Hi,

I need to get the date before count values for the field IDCAMPOCHECKITEM, aggr by NMPROPRIEDADE like in the table bellow:

DATE BEFORE

- can't use above or bellow functions as the user may change de table ordering.
- Count({$<DACHECK={"$(=max(DACHECK,2))"}>} IDCAMPOCHECKITEM) will be evaluated only once, so it wont work.

Appreciate any help on this... Thank You.
Henrique

Re: Date before for each row

Anil_Babu_Samineni — Wed, 21 Oct 2020 16:06:45 GMT

Try this

Below(Count(IDCAMPOCHECKITEM),1)

Re: Date before for each row

sfbi — Wed, 21 Oct 2020 17:11:40 GMT

As I mentioned the below() wont work as expect, because if user change the table order field the result will be set to null on this case. Also, if I select only one date, it will lose the reference and the result will be null.

Re: Date before for each row

Anil_Babu_Samineni — Wed, 21 Oct 2020 17:15:43 GMT

Even If order changed, It should work. Can you help where it is working?

Re: Date before for each row

sfbi — Wed, 21 Oct 2020 17:56:51 GMT

Help: "For charts with more than one dimension, the results of expressions containing theAbove,Below,Top, andBottomfunctions depend on the order in which the column dimensions are sorted byQlik Sense.Qlik Senseevaluates the functions based on the column segments that result from the dimension that is sorted last. The column sort order is controlled in the properties panel underSortingand is not necessarily the order in which the columns appear in a table."

As you can see, if I order by dacheck no results.

order by DACHECKorderby NMPROPRIEDADE

Also, if I select only one date, it wont show the date before count, as there are now rows below on the table.

I might need to get the result from script probably....

Re: Date before for each row

Kushal_Chawda — Wed, 21 Oct 2020 20:54:53 GMT

@sfbi try with sortable aggr like below. It will keep the actual sorting as you required

=sum(aggr(if(NMPROPRIEDADE<>Below(total NMPROPRIEDADE),Count({$<DACHECK={"$(=max(DACHECK,2))"}>} IDCAMPOCHECKITEM) , below(total Count({$<DACHECK={"$(=max(DACHECK,2))"}>} IDCAMPOCHECKITEM))),NMPROPRIEDADE,(DACHECK,(NUMERIC,DESCENDING))))

Re: Date before for each row

sfbi — Thu, 22 Oct 2020 14:55:26 GMT

Hi @Kushal_Chawda ... this expr just show the result for the last date:

Re: Date before for each row

sfbi — Fri, 23 Oct 2020 14:24:00 GMT

This bring the correct result, regardless of table ordering. Also needed to set the correct order by within the scrip when loading data, NMPROPRIEDADE, IDCAMPOCHECK DESC to get the appropriate result.

could also use below function.

AGGR(Above(count(DISTINCT {<IDCAMPOCHECK=>} IDCAMPOCHECKITEM),-1,RowNo()),NMPROPRIEDADE,IDCAMPOCHECK)