Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
sfbi
Creator
Creator

Date before for each row

Hi,

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

DATE BEFOREDATE 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

 

Labels (4)
1 Solution

Accepted Solutions
sfbi
Creator
Creator
Author

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)

 

 

sfbi_0-1603462591187.png

View solution in original post

7 Replies
Anil_Babu_Samineni

Try this

Below(Count(IDCAMPOCHECKITEM),1)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sfbi
Creator
Creator
Author

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sfbi
Creator
Creator
Author

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 DACHECKorder by DACHECKorderby NMPROPRIEDADEorderby 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
Creator
Creator
Author

Hi @Kushal_Chawda ... this expr just show the result for the last date:
sfbi_1-1603378489494.png

 

sfbi
Creator
Creator
Author

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)

 

 

sfbi_0-1603462591187.png

View solution in original post