topic AW:greatest number in QlikView

greatest number

Thu, 20 Jan 2011 09:49:04 GMT

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

greatest number

tresB — Thu, 20 Jan 2011 10:03:52 GMT

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

AW:greatest number

Thu, 20 Jan 2011 10:09:47 GMT

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

AW:greatest number

Thu, 20 Jan 2011 10:36:26 GMT

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

AW:Re: AW:greatest number

Thu, 20 Jan 2011 13:26:18 GMT

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.

AW:Re: AW:greatest number

Mon, 24 Jan 2011 08:20:31 GMT

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

AW:Re: AW:Re: AW:greatest number

Tue, 25 Jan 2011 09:34:03 GMT

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".

AW:Re: AW:Re: AW:greatest number

Thu, 27 Jan 2011 09:49:31 GMT

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

AW:Re: AW:Re: AW:greatest number

lironbaram — Thu, 27 Jan 2011 11:30:01 GMT

hei

attach is an example

hope its helps you

AW:Re: AW:Re: AW:greatest number

Thu, 27 Jan 2011 14:32:50 GMT

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

AW:Re: AW:Re: AW:greatest number

lironbaram — Thu, 27 Jan 2011 14:39:49 GMT

hei

if you want to calculate it inside an expression

try this formula

if(rank(count(ID))=1,classification

)

AW:Re: AW:Re: AW:greatest number

Thu, 27 Jan 2011 15:04:20 GMT

Hello,

When I try the new formula I Recived "-" sign

Elad