topic Calculate indicators over several months with a filter data in QlikView

Calculate indicators over several months with a filter data

Thu, 10 Jun 2010 10:43:53 GMT

Hello

I need help on a dashboard:
This involves several things in this dashboard:

The first is an indicator that must calculate the number of people who have been active (who do make money) in the previous month and also in the month select the date filters.
(Eg June 2010 is selected, we must show the number of people working for this month and active month of May 2010 also)

The second is an indicator that needs to get the number of active users last month when we select a particular month (eg June 2010 is selected, we must show the number of people working for this month and the number of people activates the month of May 2010)

Here are the different formulas that I use:

[Code]Indicator Acitve customer last month:
({$<$( vSetAnalysisSPLM count)>) DISTINCT KEY_USER_MARGE)

Customers Active this Month:
count (DISTINCT KEY_USER_MARGE)

New active customers:
(Sum (($ <USER_MARGE.Cohorte={"> = $ (monthstart = (max ([Date ])))<=$(= max ([Date ]))"}>} [USER_MARGE.Nb user ID] ))

No. Customers 1st deposit margin last month and this month:
count (($ <USER_MARGE.Cohorte={"> = $ (= Addmonths (monthstart (max ([Date]), -1)))
<=$(= AddMonths (max ([Date]), -1 ))"}>} DISTINCT KEY_USER_MARGE)

No. Active Customers Both last month and this month:
count (($ <USER_MARGE.Cohorte={"> = $ (= Addmonths (monthstart (max ([Date]), -1 )))<=$(= AddMonths (max ([Date]), -1)) ")>) DISTINCT KEY_USER_MARGE)
[/Code]
Cohort is the first deposit date of the person.

how I could calculate these indicators?
Thank you in advance for your help:)
Please find attached here http://dl.free.fr/oRlsZfzFi an example file QVW I hope it will work.

Sincerely,
Bruno

Calculate indicators over several months with a filter data

fernandotoledo — Thu, 10 Jun 2010 13:07:50 GMT

Olá Bruno!

I think you can create a flag in the script, linked by Customer&Month like:

FACT_TABLE:
LOAD
 ...,
 ...,
 Data,
 Customer,
 autonumber(Monthname(Data)&Customer) as Key,
FROM XPTO.QVD (qvd)
;

FLAG:
LOAD DISTINCT
 autonumber(Monthname(Data)&Customer) as Key,
 '1' as FlagAtivo
RESIDENT FACT_TABLE
;

This way you can make the count of active customers as SUM(FlagAtivo) and compare diferent periods with simple Set Analisys expressions...

hope it helps,

Abç,

Fernando

Calculate indicators over several months with a filter data

Thu, 10 Jun 2010 13:41:51 GMT

Hello,

Thanks for your response, but I don't know what is a flag?

How I can use this in the QVW to calculate the indicators?

Calculate indicators over several months with a filter data

fernandotoledo — Thu, 10 Jun 2010 18:14:05 GMT

A flag is a field that you use as a reference in some calculations.

If you create a table with a column named "Flag" made of 1´s wich has a row for each active customer each month, the expression for counting them is just sum(flag)

Calculate indicators over several months with a filter data

Fri, 11 Jun 2010 09:26:46 GMT

a okay, but this table is very Huge. I have 5 years and more of 2 millions of Users.

I can do this i think they are an other solutions no i hope ?