How would calculate weekly streaks of sales meeting quota?

[dwighttrumbower]

Weekly data. Current week

I need to create a streak column showing when sales was >= quota for a week. 

If North also meet the criteria for the previous week then it would show 2 vs 1. The idea is to show how many weeks as possible for a streak. 

If I had four regions, it is possible that the streak column could hold the following.

North   4
South  2
East  0
West  -2

Is this something I can do with set analysis or create a computed column on the load?

Thanks

[nevopotokcloudinary]

Nice use case. I’d probably calculate the streak in the script so the chart stays simple.

Example idea (per sales rep / region):

StreakData: LOAD SalesRep, Week, Sales, Quota, If(Sales >= Quota, 1, 0) as MetQuota Resident YourSource ORDER BY SalesRep, Week; Final: LOAD *, If(MetQuota = 1, Peek('Streak') + 1, 0) as Streak Resident StreakData ORDER BY SalesRep, Week; DROP TABLE StreakData;

This way Streak shows how many consecutive weeks that rep has met quota, resetting to 0 when they miss. Then just use Max(Streak) in your chart.

[Chanty4u]

Try this 

RangeSum(

  Above(

    Aggr(

      If(Sum(SalesAmount) >= Sum(Quota), 1, 0),

      Region, WeekStart

    ),

    0,

    RowNo(

)

  )

)

[Nagaraju_KCS]

May be try this 

TempTable:
LOAD
   Region,
   Week_ID,
   Sales,
    Quota,
   IF(Sales >= Quota, 1, 0) AS QuotaMetFlag
FROM YourFilePath
ORDER BY Region ASC, Week_ID ASC;    // Order by clause should be in resident load

FinalTable:
LOAD
   *,
   IF(QuotaMetFlag = 1,
   IF(Region = Peek('Region'), Peek('CurrentStreak') + 1, 1),
   IF(Region = Peek('Region'), 0, 0)) AS CurrentStreak
RESIDENT TempTable;

DROP TABLE TempTable;