topic Re: Ranking Question in QlikView

Ranking Question

Anonymous — Fri, 24 Apr 2015 20:01:26 GMT

I have a QV app that ranks sales employees based on total points earned. The ranking works well when I use a 30 day period or select a specific branch, but when I select a specific date as the max date, the ranking is null if there are no records for that specific date (see examplese below).

All Branches looks fine.

Single Branch looks fine:

Branch with Date Selection:

When I select a Date (used as the max date for a 30 day period), the ranking is null for any item that does not have a record with that specific date (in this case 3/18/2015) on the file. There are records in the 2/16/2015 to 3/18/2015 date range (as you can see on the chart) but none specifically on 3/18. This appears to cause the Rank function to fail. I tried adding a blank record for one of the sales associates with a 3/18 date. When I reload, that record is then shown with a ranking.

Any thoughts as to how to fix this issue?

Thanks for your help.

Ed T.

Re: Ranking Question

ramoncova06 — Fri, 24 Apr 2015 20:10:17 GMT

can you share your Rank and aggregation expressions

Re: Ranking Question

sunny_talwar — Fri, 24 Apr 2015 20:11:30 GMT

Do you want to see the rank on that specific date or do you want to see the rank for total points within last 30 days???

Re: Ranking Question

Anonymous — Fri, 24 Apr 2015 20:16:50 GMT

I can not provide the QV app, but my aggregation is done in the script as follows:

LOAD

[Associate Name],
[Account Open Date] AS S_Activity_Date,
[Referrals Factor] AS S_Referrals_Factor,
[Cross Sale Factor] AS S_Cross_Sale_Factor,
[Closed Sales Factor] AS S_Closed_Sales_Factor,
COUNT(1) as S_NbrOfSales,
SUM([Credit Points]) AS S_IncentiveAmount
Resident Retail_Incentive
Group By [Associate Name],[Account Open Date],[Referrals Factor],[Cross Sale Factor],[Closed Sales Factor]
;

The rank expressions is as follows:

AGGR(Rank(SUM({$<S_Activity_Date={'>=$(vMaxDateMinus30)<=$(vSelectedMaxDate)'},S_Activity_Date=>}S_IncentiveAmount)), [Associate Name])

Re: Ranking Question

Anonymous — Fri, 24 Apr 2015 20:17:24 GMT

I want to see the ranking for the last 30 days from the date that is selected

Re: Ranking Question

sunny_talwar — Fri, 24 Apr 2015 20:21:59 GMT

Try this may be:

=If(GetSelectedCount(S_Activity_Date) = 1,

Rank(Sum({<S_Activity_Date = {">=$(vMaxDateMinus30) <= $(vSelectedMaxDate)"}>} [Total Points])),

Rank(Sum([Total Points])))

Best,

Sunny

Re: Ranking Question

ramoncova06 — Fri, 24 Apr 2015 20:39:16 GMT

and I want to assume that you your aggregation is

SUM({$<S_Activity_Date={'>=$(vMaxDateMinus30)<=$(vSelectedMaxDate)'},S_Activity_Date=>}S_IncentiveAmournt)

right ?

do you have another field you can use instead of S_Activity_Date?

perhaps adding a [Account Open Date] AS S_Activity_Date1,

I have seen some issues when a field is being excluded and used to filter inside of the same set analysis

Re: Ranking Question

Anonymous — Fri, 24 Apr 2015 20:46:30 GMT

I do not have another date field to use. My other thought is to create a record for each sales associate for every day with zero values. This way each day will be represented even though there is no activity.

Not sure how to write the script to create these records.

Thanks for the help.

Re: Ranking Question

ramoncova06 — Fri, 24 Apr 2015 20:50:20 GMT

at first just try adding this in your script

[Account Open Date] AS S_Activity_Date1

and here is a post on how to generate missing data

Generating Missing Data In QlikView

Re: Ranking Question

Anonymous — Fri, 24 Apr 2015 20:51:53 GMT

Thanks, Sunny. I tried that but it did not work. All the reankign now say 1.

I need to head out for the day. I will look more at this over the weekend.