Help required in creating aggregation table!

[#1]

Hi All

The performance of one of my pivot table is very bad as it does lots of calcuation in the front end. So thought of creating the aggregation table so as to improve the performance of it. As I never created such aggregation table before can some one help me out on this please. I have given you the details about the tables as well as the pivot table. Also have attached the sample application as well.

Following are the tables(Data & Calendar) which are there in the attached application:

Data:

LOAD A1,
A2,
A3,
A4,
A5,
A6,
A7,
A8,
A9,
A10,
A11,
A12,
A13,
A14,
TimeStamp(A15) as StartDate,
TimeStamp(A16) as EndDate,
A17 as StartDate1,
A18 as EndDate1,
A19,
A20
FROM

(ooxml, embedded labels, table is Sheet1);

Calendar:

Load *,
Week(date) as Week,
Year(date) as Year,
WeekName(date) as WeekName,
MonthName(date) as MonthName
;

LOAD DISTINCT
DATE(StartDate+IterNo()-1) AS date
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;

Where as the pivot table contains:

Dimension:

A1

A10

StartDate

EndDate

Time(Group(Cyclic): Week,Year,WeekName,MonthName)

Expression:

SUM(IF(StartDate<=date AND EndDate>=date,1))

[#2]

Hi Erich Shiino

Yes I am getting the right result. Only concern is the performance.

I didn't understand what is that you have meant above. Could you please explain me in detail?

[erichshiino]

The example I created does not work for you, does it?

It can increase performance, we just need to adjust the expression but I don't know what your expression really is to make it work on my example.

[#4]

Yes it works and performance has been improved as well. Only concern is the expression. I also don't know how to make it work. It looks like a simple 'IF Statment' only but don't know how it works. I got fed up to understand how it works 😞

[erichshiino]

I'd like to understand this result to be able to reproduced it in the improved pivot table:

A1	A10	StartDate	EndDate	Time	2010/52
21268	Qpksnr g vfmdlz Cfxúe Vqsbdasw	15/03/2010 11:51:00	01/04/2011 09:55:00		36

Why this result within one week is 36? Shouldn't it be 7?

[#6]

Hi Erich Shiino

Even I also don't know what is that '36' in that. Also I don't wanted to display any values in it. I just wanted the background color start from StartDate and ends at the EndDate. So as to avoid displaying that numbers in the background I gave the same condition from the text as well.

For example:

In the above output in the green background, color of the number is also in green. So the numbers will not be visible in that.

But I guess there is something which that number does thats why they are using the chart. Still I couldn' t understand what is the number does there.