33 Replies Latest reply: Feb 7, 2017 2:03 AM by Agrim Sharma

# how can we group values?

i have this data i want to see count of different phases vs % classification as 0-25%, 26-50%,51-75%, 76-100%

• ###### Re: how can we group values?

Are you using a crosstable load?

• ###### Re: how can we group values?

thanks a lot for your kind reply,

No,

i have encountered an error, chart is not showing actual count of values. please help

i have used this script

"NAME OF PROJECT (Code)" ,

Phase,

"Opportunity Phase Completion %" as Classification ,

"Global Weather Situation"

FROM [lib://PPM/Project Portfolio - New.xlsx]

(ooxml, embedded labels, table is [Project Status]);

concatenate

"NAME OF PROJECT (Code)",

Phase,

"Feasibility Phase Completion %" as Classification,

"Global Weather Situation",

FROM [lib://PPM/Project Portfolio - New.xlsx]

(ooxml, embedded labels, table is [Project Status]);

concatenate

"NAME OF PROJECT (Code))" ,

Phase,

"Project Phase Completion %" as Classification,

"Global Weather Situation",

FROM [lib://PPM/Project Portfolio - New.xlsx]

(ooxml, embedded labels, table is [Project Status]);

Formula for making ratio:-used in bar chart

Dimension 1

Phase

Dimension 2

IF ([Classification] <= 0.25 , '0-25' ,

IF ([Classification]  >= 0.26 and  [Classification] <= 0.50,  '26-50%',

IF ([Classification]  >= 0.51 and  [Classification] <= 0.75,  '51-75%',

IF ([Classification]  >= 0.76 and  [Classification] <= 1,  '76-100%',

'Null'))))

Measure 1

count({\$<Phase={ 'Project', 'Opportunity' , 'Feasibility' }>} distinct  [NAME OF PROJECT (Code)] )

problem is bar chart its showing wrong values.

• ###### Re: how can we group values?

Why do you think they are wrong values? Would you be able to share your qvf if you have already created one for the sample data provided? It will save me time

• ###### Re: how can we group values?

yes i am sending please wait

• ###### Re: how can we group values?

please accept my follow request if possible

• ###### Re: how can we group values?

I have already done that

• ###### Re: how can we group values?

Why do you think the numbers are wrong? Would you be able to share the correct numbers for this chart?

• ###### Re: how can we group values?

all the 'Projects' are 37 in total but its showing in '75-100%' only the count of 'feasibility' is coming correct

• ###### Re: how can we group values?

i added my dashboard(.qvf) and data file can you please check it out? i will be very thankful

• ###### Re: how can we group values?

Little busy right now... but will surely check as soon as I get home

• ###### Re: how can we group values?

its ok i will really wait for your response, thank you:)

• ###### Re: how can we group values?

Where is your qvf?

• ###### Re: how can we group values?

Hello,

This is my .qvf and its data file

• ###### Re: how can we group values?

i have attached files

• ###### Re: how can we group values?

Hi, did you find my mistake? in formula or in script?

• ###### Re: how can we group values?

Sorry, From here what was the error you faced? What you are expecting to see

Script is ok to me

And even Formula is fine for me

IF (Classification <= 0.25 , '0-25%' ,

IF (Classification  >= 0.26 and  Classification <= 0.50,  '26-50%',

IF (Classification  >= 0.51 and  Classification <= 0.75,  '51-75%',

IF (Classification  >= 0.76 and  Classification <= 1,  '76-100%',

'Null'))))

• ###### Re: how can we group values?

i am not getting correct count. actual values are as follows

but its showing 61 thats wrong, can you please cross check in excel file? filter phase=project then, see count as 37 but chart is showing 61, i made this bar by selecting only project and its project completion%.

• ###### Re: how can we group values?

i want to show it like mutual completion % (Project, Feasibility, Opportunity) Vs. Number of Project under 0-25%, 26-50%, 51-75% and 76-100%

• ###### Re: how can we group values?

can i add any other field in this?? with classification?

IF (Classification <= 0.25 , '0-25%' ,

IF (Classification  >= 0.26 and  Classification <= 0.50,  '26-50%',

IF (Classification  >= 0.51 and  Classification <= 0.75,  '51-75%',

IF (Classification  >= 0.76 and  Classification <= 1,  '76-100%',

'Null'))))

• ###### Re: how can we group values?

you might be hitting rounding problems which your bucket statement may not handle. i would simplify the statement (as well as make it more reliable). In load script:

If(Classification <= 0.25 , '0-25%' ,

If(Classification <= 0.50,  '26-50%',

If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class

This assumes that call values of Classification are between 0 and 1.

• ###### Re: how can we group values?

"NAME OF PROJECT (Code)",

Phase,

"Opportunity Phase Completion %" as Classification

//"Feasibility Phase Completion %",

//"Project Phase Completion %"

FROM [lib://Test/SampleData.xlsx]

(ooxml, embedded labels, table is Sheet2);

concatenate

"NAME OF PROJECT (Code)",

Phase,

//"Opportunity Phase Completion %",

"Feasibility Phase Completion %" as Classification

//"Project Phase Completion %",

FROM [lib://Test/SampleData.xlsx]

(ooxml, embedded labels, table is Sheet2);

concatenate

"NAME OF PROJECT (Code)" ,

Phase,

//"Opportunity Phase Completion %",

//"Feasibility Phase Completion %",

"Project Phase Completion %" as Classification

FROM [lib://Test/SampleData.xlsx]

(ooxml, embedded labels, table is Sheet2);

can you please tell me where exactly i should write your statement?

• ###### Re: how can we group values?

yes

• ###### Re: how can we group values?
```Data:
If(Classification <= 0.25 , '0-25%' ,
If(Classification <= 0.50,  '26-50%',
If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class
;
"NAME OF PROJECT (Code)",
Phase,
"Opportunity Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate(Data)
If(Classification <= 0.25 , '0-25%' ,
If(Classification <= 0.50,  '26-50%',
If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class
;
"NAME OF PROJECT (Code)",
Phase,
"Feasibility Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate(Data)
If(Classification <= 0.25 , '0-25%' ,
If(Classification <= 0.50,  '26-50%',
If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class
;
"NAME OF PROJECT (Code)",
Phase,
"Project Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);
```

• ###### Re: how can we group values?

please see my files

• ###### Re: how can we group values?

Hello Agrim,

I have made few changes to the expression in the chart. please find the below attachment. I have got the result as below. See if it can help you.

• ###### Re: how can we group values?

this is exactly what i wanted ,thank you so much, this values that you found is real.

• ###### Re: how can we group values?

can you please send me expression by pasting it here? please

• ###### Re: how can we group values?

Hi Agrim,

In the backend, I have

LOAD [NAME OF PROJECT (Code)],
Phase,
num( [Opportunity Phase Completion %],'#,##0%') as  [Opportunity Phase Completion %],
num( [Feasibility Phase Completion %], '#,##0%')  as [Feasibility Phase Completion %],
num( [Project Phase Completion %], '#,##0%') as [Project Phase Completion %]
FROM
(ooxml, embedded labels, table is Sheet2);

In the Frontend:

Dimension: Stage

Expression 1:  (0-25)

=if(Phase='Project', count(if(([Project Phase Completion %]>='0%' and [Project Phase Completion %]<='25%') ,[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='0%'  and [Opportunity Phase Completion %]<='25%' ) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='0%' and [Feasibility Phase Completion %]<='25%') ,[NAME OF PROJECT (Code)]
)),
)))

Expression 2: (26-50)

=if(Phase='Project', count(if(([Project Phase Completion %]>='26%' and [Project Phase Completion %]<='50%') ,[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='26%'  and [Opportunity Phase Completion %]<='50%' ) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='26%' and [Feasibility Phase Completion %]<='50%') ,[NAME OF PROJECT (Code)])),
)))

Expression 3: (51-75)

=if(Phase='Project', count(if(([Project Phase Completion %]>='51%' and [Project Phase Completion %]<='75%') ,[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='51%'  and [Opportunity Phase Completion %]<='75%' ) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='51%' and [Feasibility Phase Completion %]<='75%') ,[NAME OF PROJECT (Code)])),
)))

Expression 4: (75-100)

=if(Phase='Project', count(if(([Project Phase Completion %]>='76%' and [Project Phase Completion %]<='100%') or isnull([Project Phase Completion %]),[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='76%'  and [Opportunity Phase Completion %]<='100%' ) or  isnull([Opportunity Phase Completion %]),[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='76%' and [Feasibility Phase Completion %]<='100%') or  isnull([Feasibility Phase Completion %]),[NAME OF PROJECT (Code)])),
)))

Expression 5: Null

=if(Phase='Project', count(if(isnull([Project Phase Completion %]),[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(isnull([Opportunity Phase Completion %]) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(isnull([Feasibility Phase Completion %]) ,[NAME OF PROJECT (Code)])),
)))

• ###### Re: how can we group values?

its absolutely Perfect , thanks a lot for your precious time and understanding my concern. please accept my follow request.

• ###### Re: how can we group values?

Hello Agrim,

I have made few changes to the expression in the chart. please find the below attachment. I have got the result as below. See if it can help you. QVW is attached below.

• ###### Re: how can we group values?

hello friend i have qlik sense not qlik view can you send me changes in comments? please