Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.
.
Data:
LOAD *,
If(Classification <= 0.25 , '0-25%' ,
If(Classification <= 0.50, '26-50%',
If(Classification <= 0.75, '51-75%', '76-100%'))) as Class
;
LOAD
"NAME OF PROJECT (Code)",
Phase,
"Opportunity Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);
Concatenate(Data)
LOAD *,
If(Classification <= 0.25 , '0-25%' ,
If(Classification <= 0.50, '26-50%',
If(Classification <= 0.75, '51-75%', '76-100%'))) as Class
;
LOAD
"NAME OF PROJECT (Code)",
Phase,
"Feasibility Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);
Concatenate(Data)
LOAD *,
If(Classification <= 0.25 , '0-25%' ,
If(Classification <= 0.50, '26-50%',
If(Classification <= 0.75, '51-75%', '76-100%'))) as Class
;
LOAD
"NAME OF PROJECT (Code)",
Phase,
"Project Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);
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.
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.
.
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)])),
)))