topic Re: Load order sort with Aggr in QlikView

Load order sort with Aggr

Kushal_Chawda — Thu, 20 Jul 2017 12:17:28 GMT

Hi All,

I have attached the sample data and the QVW file.

In QVW file there is one straight table created in which there is one expression which calculates the status "New" or "Repeated".

Now, my straight table dimensions are sorted as required from back end itself (check the order by in script).

Straight table works fine as expected. What I want is that, I want to show the count of "Repeated" status in text object.

If you export the straight table in excel, count of the repeated status is 1306, which I want to show in text object.

Note: User can select anything from dimension and count should work dynamically based on selection

I tried the same expression using Aggr, but seems to be wrong. May be it's easy but get stuck.

Re: Load order sort with Aggr

sunny_talwar — Thu, 20 Jul 2017 13:13:19 GMT

May be this

=Count(DISTINCT Aggr(if(START_DATE_DEFECT1-Below(TOTAL START_DATE_DEFECT1,1,1)>731 or Below(TOTAL Repeated_Defect1,1,1)<>Repeated_Defect1,0,COMPLAINT_NO_D1&COMPLAINT_DATE_D1), COMPLAINT_NO_D1, COMPLAINT_DATE_D1))

Re: Load order sort with Aggr

Anil_Babu_Samineni — Thu, 20 Jul 2017 13:18:13 GMT

I have one funny solution with me? I assume, You need some aggregation with Complaint No & Date and Count? But, I am not done that my self. May be Sunny has some answer

Re: Load order sort with Aggr

Anil_Babu_Samineni — Thu, 20 Jul 2017 13:19:28 GMT

Funny STAR. Nice Boss

Re: Load order sort with Aggr

Kushal_Chawda — Thu, 20 Jul 2017 13:22:59 GMT

I sunny, thanks for your response, but when I select one GBU="BCS", it's not working

Re: Load order sort with Aggr

Anil_Babu_Samineni — Thu, 20 Jul 2017 13:25:04 GMT

When you select GBS you want to show 144 ???

Re: Load order sort with Aggr

Kushal_Chawda — Thu, 20 Jul 2017 13:29:35 GMT

Yes correct

Re: Load order sort with Aggr

sunny_talwar — Thu, 20 Jul 2017 13:30:41 GMT

Checking

Re: Load order sort with Aggr

sunny_talwar — Thu, 20 Jul 2017 13:41:48 GMT

This seems to be working

=Sum(Aggr(if(START_DATE_DEFECT1-Below(TOTAL START_DATE_DEFECT1,1,1)>731 or Below(TOTAL Repeated_Defect1,1,1)<>Repeated_Defect1,0,1), GBU_D1, BU_D1, COMPLAINT_NO_D1, START_DATE_DEFECT1, Facility_D1, COMPLAINT_DATE_D1, START_DATE_DEFECT1, BU_DEFECT1, DEFECT_LOCATION1, Repeated_Defect1))

Re: Load order sort with Aggr

Kushal_Chawda — Thu, 20 Jul 2017 14:15:42 GMT

Great man.

Re: Load order sort with Aggr

sunny_talwar — Thu, 20 Jul 2017 14:17:27 GMT

Thank you ...lol

Re: Load order sort with Aggr

Kushal_Chawda — Thu, 20 Jul 2017 14:18:56 GMT

ye bhi chal raha

=Sum(Aggr(if(START_DATE_DEFECT1-Below(TOTAL START_DATE_DEFECT1,1,1)>731 or Below(TOTAL Repeated_Defect1,1,1)<>Repeated_Defect1,0,1), GBU_D1, BU_D1, COMPLAINT_NO_D1, Facility_D1, COMPLAINT_DATE_D1, START_DATE_DEFECT1, BU_DEFECT1, DEFECT_LOCATION1, Repeated_Defect1))