Date before for each row

[sfbi]

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

[Anil_Babu_Samineni]

Try this

Below(Count(IDCAMPOCHECKITEM),1)

[sfbi]

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.

[Anil_Babu_Samineni]

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

[sfbi]

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....

[Kushal_Chawda]

@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))))

[sfbi]

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

[sfbi · Accepted solution]

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)