Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to get the date before count values for the field IDCAMPOCHECKITEM, aggr by NMPROPRIEDADE like in the table bellow:
- 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
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)
Try this
Below(Count(IDCAMPOCHECKITEM),1)
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.
Even If order changed, It should work. Can you help where it is working?
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.
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....
@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))))
Hi @Kushal_Chawda ... this expr just show the result for the last date:
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)