Get the avg of fact column values of last 5 days

[saumil287]

Hi Team,
I am having 1 scenario, I need to create a query item that creates the avg value of last 5 days.

EX:
From the below dataset, as you can see, Last 5 Day Avg column is having value as 58, which is the avg amt of last 5 days i.e from 03-02-17 to 07-02-17.
I tried a case stmt but the values are coming blank
CUST_NO| PAY_DT  | AMT | Last 5 Day Avg |
  1            01-02-17   50        58
  2            02-02-17   60        58
  3            03-02-17   50        58
  4            04-02-17   60        58
  5            05-02-17   60        58
  6            06-02-17   50        58
  7            07-02-17   70        58

Thanks
saumil287

[hespora]

Code Select Expand


total (
  case when rank ( [PAY_DT] desc for report ) <= 5 then [AMT] else 0 end
  for report
)
/ 5


[saumil287]

Hi Hespora,

Thanks much for your reply.

I forgot to add 1 more point.
The dates can be repeated so I cannot use the rank function.

So I tried as "total(Case when to_date(?ToDate?,'dd-Mon-YYYY') between to_date(?ToDate?,'dd-Mon-YYYY') and _add_days(to_date(?ToDate?,'dd-Mon-YYYY'),-7) then [NetAmt] else 0 for Report)/5"

Here PAY_DT is actually a parameter as to_date(?ToDate?,'dd-Mon-YYYY').

It goes into else stmt and giving me values as 0 for entire rows.

Thanks
saumil287

[hespora]

something about that is still off with your comparison in the case statement. You're comparing if "Parameter between Parameter and Parameter-7". While that should always evaluate as true rather than false, you're not comparing against the date in your data source.

Try

Code Select Expand


"Case when [PAY_DT] and _add_days(to_date(?ToDate?,'dd-Mon-YYYY'),-7) then [NetAmt] else 0

first. If that correctly displays values for the rows where the date fits and zeroes where it doesn't, wrap the total function around it.

[saumil287]

Hi hespora,

I tried as total(case when Date between [last 5 days] and [selected date] then Net Amt end for [last 5 days],[selected date])

It worked, now I am having a diff scenario
CUST_NO|Class No| PAY_DT  | AMT | Last 5 Day Avg |
  1               2          01-02-17   50        58
  2               2          02-02-17   60        58
  3               2          03-02-17   50        58
  4               3          04-02-17   60        60
  5               3          05-02-17   60        60 
  6               3          06-02-17   50        60
  7               3          07-02-17   70        60
It should do average based on class no

Thanks again

[saumil287]

Hi Hespora,


I added the class No in the scope after to date and it automatically calculated the average

Thanks again