Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
as you can see in the image below, the table "suscriptos activos" has values in ene-2010 (january-2010), feb-2010 and mar-2010, but this is not truth, because the first value of the table behing (subscriptions.created) start over nov-2010 (5/11/2010).
here is the syntax of "suscriptos activos":
SUM( IF( SUBSCRIPTIONS.is_subscribed = 0 , IF ( SUBSCRIPTIONS.modified > CALENDARIO_SUBSCRIPTIONS.AñoMes , 1 , 0 ), 1 ) )
Can i add same function to avoid the months without data?
Syntax of "subscriptions.crated"
CALENDARIO_SUBSCRIPTIONS:
LOAD
Year(PeriodDate) & Num(Month(PeriodDate),'00') & Num(Day(PeriodDate),'00') as %Key_CREATED_SUBSCRIPTIONS,
Year(PeriodDate) as Año,
Month(PeriodDate) as Mes,
Day(PeriodDate) as Dia,
PeriodDate as Fecha,
DUAL(Month(PeriodDate) & '-' & Year(PeriodDate),
Year(PeriodDate) & Num(Month(PeriodDate),'00')) as AñoMes,
Week(PeriodDate) as Semana,
Ceil(Month(PeriodDate)/3) as Trimestre,
(Year(PeriodDate) * 12) + Num(Month(PeriodDate)) as LineaTemporal
RESIDENT TempCalendar;
Syntax of "subscriptions table":
SUBSCRIPTIONS:
LOAD
Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_CREATED_SUBSCRIPTIONS,
city_id,
city_id as %Key_CITY_SUBSCRIPTIONS_ID,
email,
email as %Key_EMAIL,
is_voluntary,
is_subscribed,
modified,
user_id,
created,
/*user_id as %Key_USER_ID,*/
id as %Key_SUBSCRIPTION_ID
FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd)
thanks!
Mh... no entendí. podés mandar el qvw?
You can use a where statement to avoid this data. Consider the following (I have also attached a .qvw):
Table:
LOAD * INLINE [
Date, Number
4/1/12, 1
4/3/12,
4/15/12, 15
4/23/12, 23
]
WHERE Number <> '' AND Number <> NULL();
This will return a table that looks like this:
Date | Number |
---|---|
4/1/12 | 1 |
4/15/12 | 15 |
4/23/12 | 23 |
Notice that the date of 4/3/12 was avoided since it doesn't contain any data.
Hi Nicole,
thanks for your answer but the thing is i have no values for this months.
I attached the .qvw
As you will see in the attached file the table1 start to show values from nov-2010, but in table 2 there are values from ene-2010
Thanks for any advice
Gerardo
The problem is with the expression on the second chart. See the attached .qvw with a different expression. Here it doesn't show the months that don't have data. If you need help writing a different expression, I will need further explanation on what exactly you are trying to calculate.
En primer lugar, en la expresión hay un error en uno de los campos, pusiste SUBSCRIPTIONS_modified y creo que sería SUBSCRIPTIONS_AñoMes_modified.
Comentame, cómo es la cuenta? porque no entiendo la expresiòn.
No sabìa que estaba respondido. Sorry