Aggregate over multiple fields and condition

[Dipak]

Hi, I have a below table structure

Id, Date, Week, Numerator, Denominator, StartDate (Calculated Field), EndDate (Calculated Field)

Attached excel with data source data and calculations required.

Now i would like to populate Sum(Numerator), Sum(Denominator), KPI =Sum (Numerator)/sum(Denominator) and Rank based on KPI within Week against each ID. However, the sum(Numerator) and sum(Numerator) has to be same for ID between StartDate and EndDate.

I tried many things, but could not get it work.

FYI. have used Qliksense before 3 years and started again very recently.

@sunny_talwar , @swuehl  - i have seen quite a bit of succesful solutions from your side for similar query in many posts, so hopeful that you would be able to help me. Apologies for direct tag.

[sunny_talwar]

Check the attached qvf

[Dipak]

I just loaded the full year data and it seems the formula works fine except in the week where it crosses over to next year i.e. 30/12/2019. This week contains Dates from 30/12/2019,31/12/2019,1/1/2020,2/1/2020,3/1/2020,4/1/2020,5/1/2020. Is that expected. i put the forumula exactly as it is

=Aggr(RangeSum(Above(Sum(Chats), 0, vWeekNum)), [Person(Person - who) : Employee_Number], [WC Week])

[Dipak]

the above issue also happens for very first week i.e. for employee it sums up the first week and max week rather than just taking first week. Other than these 2 scenarios, it's perfectly fine. 

any way to tackle these? so far, the solution works but it would break if someone upload last year data which is a remote possibility for me and when next year starts a certainity after 7 months 🙂

[sunny_talwar]

Can you share a sample where we can see the issue?

[Dipak]

Sure. will share tomorrow with all data that i have loaded (of course randomized :-))

[Dipak]

The other problem got resolved on its own when i refreshed the data. But somehow the problem occurs for 1 particular week '26/08/2019'. Only for this week, the formular does not sum across 2 weeks, but works everywhere else. initially i though the issue was with data, but seems soemthing else.

removing data prior to Aug makes it work fine except only 1 entry.

attaching both files

file 1 all data check for weeks 19/08/2019 and 26/08/2019

file 2 check for id 10382 and week 25/11/2019 - 2 weeks sum is issue.

Though my primary problem got resolved, highlighting this issue in case it helps someone.

[Dipak]

any other way instead of rangesum function?

[Dipak]

ok. after extensive data upload and refresh, i have come to the conslusion that rangesum does not work with some records (checked and recked with many data sets). any other alternative?

attached file with one more data upload and now there are 35 entries with issue. attaching excel for cross checking

[Dipak]

Rangesum(above())worjed in the example that i sent because it was dummy data, but did not work on actual data as there are lot of weeks with no data and thus may be missing. can't substitue those with 0 as no way to know which ID, Week combination may be missing. the numbers are coming wrong for 8 weeks aggregation for many cases

@sunny_talwar Would there be any alternative other than rangesum(above)?

[sunny_talwar]

The best alternative would be to use The As-Of Table