greatest number

[#1]

Hello,
I build models that analyze IT Support Center
I have the following fields:
ID - all call have unique ID.
Classification - All call have classification.
I try to show only the classification with greatest number of calls a month.

Thank you
Elad

[tresB]

Hi,

If you want to do it at script: group the data by your classification(class) and count it. then for second time order it in descending order, then peek the first record using peek() function.

If you are trying at the front end, use AGGR function.

regards, tresesco

[#3]

Hello Elad,

I would use a chart with a calculated dimension similar to this:

=if(aggr(rank(aggr(count(ID), Month), 4, 2), Month) = 1, Classification )

HtH

Roland

[#4]

Hello,
Thanks much for the reply.
I tried to create the chart without success.
Can you give more details.
I would like eventually to have a text box (or anything else to show similar) that will contain the name of the most used classification in the max month.

Thank you

Elad

[#5]

Hello Elad,

best thing would be when you post a little example app. Then we can try to create what you need and you can use the reposted app as guideline.

RR

[#6]

Hello,

This is the model script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='? #,##0.00;?-#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYYMMDD';
SET TimestampFormat='YYYYMMDD hh:mm:ss[.fff]';
SET MonthNames='?????;??????;???;?????;???;????;????;??????;??????;???????;??????;?????';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

let V_ref_Date = year(now())-3&'-01-01' ;

FACT_HD_MODEL:
LOAD DESCRIPTION,
ID,
ITEMTYPE,
SERSHORTTEXT1,
SERVICECALLCODE10,
SERVICECALLCODE10_OBJECT_ID,
ACTUALCOST,
ACTUALDURATION,
ACTUALFINISH,
if ((IsNull(ACTUALFINISH)),'OPEN',
if ((num(ACTUALDURATION)) < 1, '< 01 Hour',
if ((num(ACTUALDURATION)) < 4, '< 04 Hours',
if ((num(ACTUALDURATION)) < 8, '< 08 Hours',
if ((num(ACTUALDURATION)) < 24, '< 24 Hours',
if ((num(ACTUALDURATION)) < 48, '< 48 Hours', '> 48 Hours')))))) as TIME_FRAME,

year(ACTUALFINISH) as ACTUALFINISH_YEAR,
if(num(month(ACTUALFINISH))>9,'Q4',
if(num(month(ACTUALFINISH))>6,'Q3',
if(num(month(ACTUALFINISH))>3,'Q2',
if(num(month(ACTUALFINISH))>0, 'Q1',)))) as ACTUALFINISH_QUARTER,
month(ACTUALFINISH) as ACTUALFINISH_MONTH,
week(ACTUALFINISH) as ACTUALFINISH_WEEK,
day(ACTUALFINISH) as ACTUALFINISH_DATE,
weekday(ACTUALFINISH) as ACTUALFINISH_DAY,
hour(ACTUALFINISH) as ACTUALFINISH_HOUR, //change
//ACTUALFINISH_HOUR, ?? ???? ? VIEW
ACTUALSTART,
year(ACTUALSTART) as ACTUALSTART_YEAR,
if(num(month(ACTUALSTART))>9,'Q4',
if(num(month(ACTUALSTART))>6,'Q3',
if(num(month(ACTUALSTART))>3,'Q2','Q1'))) as ACTUALSTART_QUATER,
month(ACTUALSTART) as ACTUALSTART_MONTH,
week(ACTUALSTART) as ACTUALSTART_WEEK,
day(ACTUALSTART) as ACTUALSTART_DATE,
weekday(ACTUALSTART) as ACTUALSTART_DAY,
(((year(ACTUALSTART)))& if(len(num(month(ACTUALSTART)))<2,'0'&num(month(ACTUALSTART)),(num(month(ACTUALSTART))))) as ACTUALSTART_YearMonth,
makedate(left(ACTUALSTART,4),mid(ACTUALSTART,6,2),mid(ACTUALSTART,9,2)) as ACTUALSTART_date,
mid(ACTUALSTART,9,2) as ACTUALSTART_DAYM,
hour (ACTUALSTART) as Hour,
maketime(hour (ACTUALSTART),minute(ACTUALSTART))as Full_Hour,
if((weekday(ACTUALSTART)='Sat') or (weekday(ACTUALSTART)='Fri') or ((hour (ACTUALSTART)>'18')and (maketime(hour (ACTUALSTART),minute(ACTUALSTART))<'7')),'not work hour','work hour' )as work,
CALLER_NAME,
CALLER_OBJECT_ID,
CATEGORY,
CATEGORY_OBJECT_ID,
CATEGORYSERVICETODAY,
CATEGORYSERVICETODAY_OBJECT_ID,
CLASSIFICATION,
CLASSIFICATION_OBJECT_ID,
mid(CALLERORGANIZATION_NAME1,9) as CALLERORGANIZATION_NAME1,
if(len(num((month(left(ACTUALSTART,10)))))=2,(num((month(left(ACTUALSTART,10))))),'0'&(num((month(left(ACTUALSTART,10)))))) as month,
left(ACTUALSTART,4) & if(len(num((month(left(ACTUALSTART,10)))))=2,(num((month(left(ACTUALSTART,10))))),'0'&(num((month(left(ACTUALSTART,10)))))) as Year_Month,
date(filetime())as FileDate


FROM \\QVD\FACT_HD_MODEL.qvd (qvd)
where STATUS<>'Void';

LOAD * INLINE [
Time
Start
Finish
];

Every call has a unique ID.
I'm looking for the Classification with the most calls in particular month.
Thank you
Elad

[#7]

Hi Elad,

sorry, but I can't use your load-script. Instead I created a little exam app to play with. I created some inline-data with the fields from your first post. Take a look at it.The concat()-statements in the second expression are neccesary because there can be more than one max(value). This case you can see for month = "03".

RR

[#8]

Hello,

Thank you again for your help.

I have table like this:

ID,	YearMonth,	classification,
1	04-06-2010	Delete user
2	05-06-2010	Outlook
3	06-06-2010	Outlook
4	07-06-2010	Outlook
5	01-07-2010	Delete user
6	02-07-2010	Internet
7	03-07-2010	Outlook
8	04-07-2010	Outlook
9	05-07-2010	Internet
10	06-07-2010	Internet

I try find the most used classification in uniq month.

Here the classification in 06/2010 is Outlook and in 07/2010 is Internet.

I'm looking only the calssification name and not the number.

Thank you again

Elad

[lironbaram]

hei

attach is an example

hope its helps you

[#10]

Shalom Roi,

I add this line to expressions place in chart wizard:

=max(aggr(count(ID),CLASSIFICATION,ACTUALSTART_Year_Month))

I recived only the number of bigger classification and not the name of the classification.

Thank you

Elad