Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
im trying to run the script below but an error legend appear which saids
"Field not found - <AñoMes>
AsOf:
Load distinct AñoMes
Resident CALENDARIO_SUBSCRIPTIONS"
"Table not found
Left Join (AsOf)
Load AñoMes as AñoMesAsOf
Resident AsOf"
"Table not found
Inner Join (AsOf)
Load *
Resident AsOf
Where AñoMesAsOf >= AñoMes"
1) Script:
CALENDARIO_SUBSCRIPTIONS:
LOAD
Year(PeriodDate) & Num(Month(PeriodDate),'00') & Num(Day(PeriodDate),'00') as %Key_MODIFIED_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;
AsOf:
Load distinct AñoMes
Resident CALENDARIO_SUBSCRIPTIONS;
Left Join (AsOf)
Load AñoMes as AñoMesAsOf
Resident AsOf;
Inner Join (AsOf)
Load *
Resident AsOf
Where AñoMesAsOf >= AñoMes;
thanks!
gerardo
great! thank you very much to both!
Hi Marcos and Swuehl,
i tried a lot of times to do the same with only subscriptions but i couldn't, i mean the full accumulation expression of
Count(DISTINCT SUBSCRIPTIONS.email) with out full accumulation expresion box enable....
could you please give me some advice?
Thanks
gerardo
Maybe something like
=aggr( rangesum( above(
Count({<CALENDARIO_SUBSCRIPTIONS.AñoMes=, SUBSCRIPTIONS.is_subscribed={0,1}>} DISTINCT SUBSCRIPTIONS.email)
,0,rowno()))
, CALENDARIO_SUBSCRIPTIONS.AñoMes)
sorry....that give me the values of each month and not the accumulation....
some other idea?
Seems to work for me:
CALENDARIO_SUBSCRIPTIONS.AñoMes | Suscriptos acum | Altas | Bajas |
---|---|---|---|
5099063 | 710482 | 63606 | |
Nov-2010 | 2099 | 2083 | 16 |
Dic-2010 | 30201 | 27672 | 430 |
Ene-2011 | 71726 | 33214 | 8311 |
Feb-2011 | 78350 | 3560 | 3064 |
Mar-2011 | 94609 | 14891 | 1374 |
Abr-2011 | 116944 | 18778 | 3565 |
May-2011 | 136219 | 17557 | 1725 |
Jun-2011 | 170877 | 32803 | 1865 |
Jul-2011 | 236217 | 62693 | 2653 |
Ago-2011 | 325069 | 79175 | 9709 |
Sep-2011 | 399641 | 70567 | 4032 |
Oct-2011 | 536844 | 132006 | 5210 |
Nov-2011 | 618367 | 75774 | 5842 |
Dic-2011 | 704834 | 79046 | 7491 |
Ene-2012 | 776441 | 65571 | 6093 |
Feb-2012 | 800625 | 21576 | 2620 |
and if I select last few month:
CALENDARIO_SUBSCRIPTIONS.AñoMes | Suscriptos acum | Altas | Bajas |
---|---|---|---|
3437111 | 367418 | 27042 | |
Oct-2011 | 536844 | 132006 | 5210 |
Nov-2011 | 618367 | 75774 | 5842 |
Dic-2011 | 704834 | 79046 | 7491 |
Ene-2012 | 776441 | 65571 | 6093 |
Feb-2012 | 800625 | 21576 | 2620 |
now is working.....thanks you!