topic Re: Only include latest 3 month set analysis in QlikView

Only include latest 3 month set analysis

Thu, 14 Aug 2014 09:24:46 GMT

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')

Re: Only include latest 3 month set analysis

Thu, 14 Aug 2014 09:27:21 GMT

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')

Re: Only include latest 3 month set analysis

tresB — Thu, 14 Aug 2014 09:29:44 GMT

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))"

Re: Re: Only include latest 3 month set analysis

Thu, 14 Aug 2014 09:36:58 GMT

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)

Re: Re: Only include latest 3 month set analysis

Thu, 14 Aug 2014 09:50:03 GMT

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. ?

Re: Only include latest 3 month set analysis

tresB — Thu, 14 Aug 2014 10:00:54 GMT

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)?

Re: Only include latest 3 month set analysis

Thu, 14 Aug 2014 10:24:22 GMT

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

Re: Only include latest 3 month set analysis

tresB — Thu, 14 Aug 2014 10:31:14 GMT

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.

Re: Only include latest 3 month set analysis

Thu, 14 Aug 2014 10:33:44 GMT

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!

Re: Only include latest 3 month set analysis

tresB — Thu, 14 Aug 2014 10:39:57 GMT

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.

Re: Only include latest 3 month set analysis

Thu, 14 Aug 2014 10:46:17 GMT

Ahh thanks for clearing!

Is there any other function that would be better to use then?