get sum of count 30th date before given date in qlikview

[#1]

hi,

I had a condition where i need to get the count of viewers for the given date and 30 days before the given date(only 30 th day before the given date).

I need to represent this in a straight table as below.

Timestamp          count          count(30 days ago) percentage

please help me how to acheive this.

[Anonymous]

Hi Kausalya,

Have you not tried my previous post expresions........

They would definately work...

[Gysbert_Wassenaar]

Use this as script:

T1:
load date#(date,'YYYY-MM-DD') as date,viewers inline
[date,viewers
2013-10-24,110
2013-09-25,136
2013-10-24,110
2013-09-25,120
2013-10-23,158
2013-09-22,120
2013-10-12,110
2013-05-11,120
2013-08-10,147
2013-09-10,120
2013-04-12,152
2013-09-24,185
2013-10-24,168
2013-09-25,120
2013-08-26,130
2013-08-25,140
2013-08-24,150
];

AsOf:
load date as report_date, date, 'current' as type
Resident T1;
load date as report_date, date(date-30) as date, '30 days before' as type
Resident T1;

In the front end use report_date in a chart as dimension and these expressions:

• Sessions: sum({<type={'current'}>}viewers)
• 30d ago: sum({<type={'30 days before'}>}viewers)
• Change: [Sessions]/[30d ago]-1

[tresesco]

define variables:

v1 =concat({1} 'sum({<date={' & chr(39)  &  date((date-30),'YYYY-MM-DD')   & chr(39) &  '} >}total viewers)' , ',')

v2= Concat( {1} num(date), ',')

Expression:

=pick(match(num(date),$(v2))+1,0,$(v1))

[#5]

Yes. I looked into it. But its not with the current date, but the date which is present in the table.

[#6]

hi wassenar,

can we achieve the above thing in the front end.

if i apply filters (month), i  am not able to see previous 30 days data for above.

[Gysbert_Wassenaar]

You can probably achieve the same thing in the front end. But it's going to be ugly, complicated and likely to perform badly. So I won't bother trying to explain how to do it. Instead try this:

Create a report_month field in the AsOf table and use that to select the month:

AsOf:

load date as report_date, date, 'current' as type, month(date) as report_month

Resident T1;

load date as report_date, date(date-30) as date, '30 days before' as type, month(date) as report_month

Resident T1;