Only include latest 3 month set analysis

[#1]

Hi,

i have this set analysis. As it is now it works when i select filters on month, but i want it to only take the lastest 3 months, because i need it on a dashboard where there is no filters.

How do i do so it only takes the latest three months?

=num(Avg(aggr(sum({<Date_CleanDato={"<$(=Date(Today()-1))"}>}TotalSessions) / (count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /

(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))) / Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct  MonthFact),MonthYear)),'#.##0')

[#2]

In Color:

=num(Avg(aggr(sum({<Date_CleanDato={"<$(=Date(Today()-1))"}>}TotalSessions)

/

(count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey)

/

(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato))))

/

Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct  MonthFact),MonthYear)),'#.##0')

[tresesco · Accepted solution]

May be like:

Date_CleanDato={"<$(=Date(Today()-1)) >=$(=MonthStart(Today(),-3))"      Or, if you want latest by dates in the app, then like:

Date_CleanDato={"<$(=Max(Date_CleanDato)) >=$(=MonthStart(Max(Date_CleanDato),-3))"

[#4]

Hmm, where should it be?

I just provided sample file.

The result in Monthly Weighted Average should be around 168.000 when the lastest three months is selected (Aug,Jul,Jun).

But it should be done with set analysis instead.

The customer is called zmmhuigme (scrambled)

[#5]

I think i almost got it but i have a problem. Since it is a weigthed average i count how many days there is.

AS for now for this customer it works, but it is because the customer has dates from the 1.jun

=num(Avg(aggr(sum({<Date_CleanDato={"<$(=Max(Date_CleanDato)) >=$(=MonthStart(Max(Date_CleanDato),-2))"}>}TotalSessions)
/ (count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=MonthStart(Max(Date_CleanDato),-2)) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /
(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))) / Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct  MonthFact),MonthYear)),'#.##0')

But i suppose with this expression:

(count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=MonthStart(Max(Date_CleanDato),-2)) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey)

i will be in problems if my dates start mid june eg. ?

[tresesco]

In that case , how do you want your expression to behave like? take the count back beyond 3 months (to complete 90 days with existing dates)?

[#7]

The count should only go back 3 months, but take the minimum date of 3 months back.

So if i only have data from 20 july to now, it should only count from 20th july

[tresesco]

That is what exactly qv does by default. If there is no dates(or even if they are there from master calendar- data/metric field would not be there) in your date field to compare, you get only data till 20th July.

[#9]

Ahhh i didnt know that the MonthStart took the latest date - i Thought it by default would count from the start month.

So i think this is correct. Thank you so much for your help!

[tresesco]

Thomas Jensen wrote:

Ahhh i didnt know that the MonthStart took the latest date - i Thought it by default would count from the start month.

So i think this is correct. Thank you so much for your help!

Let me re-correct it. MonthStart() takes(actually gives) the starting date of the month. However, since the dates :1-20 July would not have data(0), you would get the result right. Hope this helps.