Pivot table and grouping data

[#1]

I have a simple data but can't figure out on how to put the data together. This is what I have:

Location	Days	Count
Texas	0	10
Texas	1	6
Texas	4	3

I want to group the Days by duration, here are the duration description:

Duration	At the same day	<=0
	Next day	<=1
	More than 1 day	>1

This is what I want to accomplish with pivot table:

Location	Duration	Count
Texas	At the same day	1
	Next day	16
	More than 1 day	19

I've been browsing this forum but can't find any answer.

Any help is appreciated! Thank you.

[#2]

Hi,

Data:
LOAD * INLINE [
Location, Days, Count
Texas, 0, 10
Texas, 1, 6
Texas, 4, 3
];

Let vToday = 0;
Let vTodayMax= peek('Days',-1,Data);
DaysRange:
LOAD
Range,
Evaluate(Low)AS Low,
Evaluate(High) AS High
;
LOAD * INLINE [
Range; Low; High
At the same day;vToday ; vToday
Next day; vToday; vToday+1
More Than 1 day; vToday+1; vTodayMax
] (delimiter is ';')
;

JOIN(DaysRange) IntervalMatch(Days) Load Low, High Resident DaysRange ORDER BY Low ASC;

after running this script, create a pivot table with dimension as location,ranges and expresion as sum(count).

It solves your problem...

Niranjan M.

[rahulgupta]

Hey Hi Dear, I have attached a test file as per your data.... Hope this contributes in solving your issue..!! Please have a look and let me know any further issues....!!

[gauravkhare]

Hi Buddy,

from your requirment is for certain that you need to have 2 dimensions namely location and duration and the expression will the count of something.....

Now location we have and for the duration purpose you can do one thing... either go for calculated dimension or create the same in the script part itself..

Goto add calculated dimension tab and in that write the script like- if(Days>1,'greater than 1',

if(Days>4,'greater than 4', etc etc))..

so now you have got two dimensions one is city and the second is Duration..Take these two dimensions in your pivot table and take expression as count of days.

Hope you must have got an idea on how to proceed if in case you face any problem kindly send me a sample data and I will create an application for you.