topic Count Display in a Text box in QlikView

Count Display in a Text box

konidena — Fri, 15 Dec 2017 10:14:19 GMT

Hi Team,

I have the Straight table with two date columns.

Date1

Date2

I created the expression Max({<BusinessSegmentCD={OOAH}>}Date#(Date1,'YYYYMMDD')- Date#(Date2,'YYYYMMDD'))

The above expression giving the Number of days. This value is displaying in each row.

If i take this into text box, Count{<BusinessSegmentCD={OOAH}>}Date#(Date1,'YYYYMMDD')- Date#(Date2,'YYYYMMDD')),

Count is displaying and it is matching with the number of rows in straight table.

I am following the coloring technique here.

If the days difference is >15 , then the color of the cell background is Red

if the days difference is >=5 and <=15, the color of the cell background is amber

if the days difference is <5, the color of the cell background is white.

Question:

From the above expression,

1. I need to get the count into separate text box that show the count how many rows are there with the days of difference >15

2. I need to get the count into separate text box that show the count how many rows are there with the days of difference >=5 and <=15

3. I need to get the count into separate text box that show the count how many rows are there with the days of difference <5

Please help in achieving this.

Re: Count Display in a Text box

avkeep01 — Fri, 15 Dec 2017 10:20:00 GMT

Hi Sri Nivas,

You could try using an aggr function.

SUM(IF(AGGR( Max({<BusinessSegmentCD={OOAH}>}Date#(Date1,'YYYYMMDD')- Date#(Date2,'YYYYMMDD')),Date1,Date2)>5 AND AGGR(Max({<BusinessSegmentCD={OOAH}>}Date#(Date1,'YYYYMMDD')- Date#(Date2,'YYYYMMDD')),Date1,Date2)<15,1,0))

It is a bit guessing without an exmple app. So maybe there is some finetuning needed on this expression. Maybe you could upload an example?

Re: Count Display in a Text box

sunny_talwar — Fri, 15 Dec 2017 10:20:55 GMT

May be this

Sum{<BusinessSegmentCD={OOAH}>} Aggr(If(Date#(Only({<BusinessSegmentCD={OOAH}>}Date1),'YYYYMMDD') - Date#(Only({<BusinessSegmentCD={OOAH}>}Date2),'YYYYMMDD')) > 15, 1, 0), Date1, Date2))

Sum{<BusinessSegmentCD={OOAH}>} Aggr(If(Date#(Only({<BusinessSegmentCD={OOAH}>}Date1),'YYYYMMDD') - Date#(Only({<BusinessSegmentCD={OOAH}>}Date2),'YYYYMMDD')) <= 15 and If(Date#(Only({<BusinessSegmentCD={OOAH}>}Date1),'YYYYMMDD') - Date#(Only({<BusinessSegmentCD={OOAH}>}Date2),'YYYYMMDD')) >= 5, 1, 0), Date1, Date2))

Sum{<BusinessSegmentCD={OOAH}>} Aggr(If(Date#(Only({<BusinessSegmentCD={OOAH}>}Date1),'YYYYMMDD') - Date#(Only({<BusinessSegmentCD={OOAH}>}Date2),'YYYYMMDD')) < 5, 1, 0), Date1, Date2))

Re: Count Display in a Text box

avkeep01 — Fri, 15 Dec 2017 10:27:18 GMT

Hi Sri Niva,

I've build an example myself. I've attached to this post. Hope that it is working for you.