Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
Need help in achieving a requirement , Having a sample data in below format ,
With two Dimensions(Type,Level) and a Expression
The requirement :
Every Type has L1 to L10 values , so the requirement is instead of showing all the level for each Type, show only those level which satisfy below condition
Condition : N+1/N > 1 and N+2/N+1<1
For Example
NOTE:Every row value is considered as N and top of that value is considered as N+1,N+2
From the above pic N=L9-O (115) and N+1=L8-O(196) AND N+2=L7-O(156)
so by the condition = N+1/N > 1 and N+2/N+1<1
196/115 >1 true
and
156/196 <1 true
so the condition is satisfied , so for Type AGS one level will be L9-O (N)
so i have achieved this requirement by writing below expression in Dimension
IF((
Aggr(below(Count(distinct {<FY={$(vMaxU_FY)}, CLOS_HC_FLAGCFY={1}>} EMPID_IVOE))
, Type
, (LevelNo,(NUMERIC, DESCENDING))
)
/
Aggr(Count(distinct {<FY={$(vMaxU_FY)}, CLOS_HC_FLAGCFY={1}>} EMPID_IVOE), Type, Level)
) > 1
and
(
Aggr(below(Count(distinct {<FY={$(vMaxU_FY)}, CLOS_HC_FLAGCFY={1}>} EMPID_IVOE), 2)
, Type
, (LevelNo,(NUMERIC, DESCENDING))
)
/
Aggr(below(Count(distinct {<FY={$(vMaxU_FY)}, CLOS_HC_FLAGCFY={1}>} EMPID_IVOE))
, Type
, (LevelNo,(NUMERIC, DESCENDING))
)
) < 1
,
(Level)
)
By above expression i am getting N value from Level ,but want i need is instead of N value show N+1 value
in the above case instead of L9-O i need to show L8-O ,if N value is satisfied the condition then pick N+1 has out put,
so in the above expression In the place of LEVEL. wat should i need to write
Thanks
@kunkumnaveen wrote:
For AGS instead of showing L5-M,L9-O (N)as Dimension values i need to show L5-DH and L8-O (N+1)
How about the Level for AMS and CS? Would they also change to L7-O?
Calculated dimension
=Aggr(
If(
Below(Sum(DISTINCT {<FY={'2018'}>} Expression))/
Sum(DISTINCT {<FY={'2018'}>} Expression) > 1
and Below(Sum(DISTINCT {<FY={'2018'}>} Expression), 2)/
Below(Sum(DISTINCT {<FY={'2018'}>} Expression)) < 1
, Below(Level))
, Type, (LevelNo,(NUMERIC, DESCENDING)))
The only thing I really changed was to use Below(Level) instead of Level. But I also removed the multiple usage of Aggr() as it seems that you were essentially aggregating on the same fields. If Level and levelNo are not one to one for each Type, then you might have to change back to your original calculated dimension and use Below(Level).
Would you be able to share a qvw sample to see what you already have and work with it to get you to what you want?
Hello Sunny,
As requested i am attaching a sample QVW file ,with partial achieved calculated Dimension expression
Requirement
For AGS instead of showing L5-M,L9-O (N)as Dimension values i need to show L5-DH and L8-O (N+1)
Hello any suggestion ,on above requirement
Checking it now
@kunkumnaveen wrote:
For AGS instead of showing L5-M,L9-O (N)as Dimension values i need to show L5-DH and L8-O (N+1)
How about the Level for AMS and CS? Would they also change to L7-O?
Calculated dimension
=Aggr(
If(
Below(Sum(DISTINCT {<FY={'2018'}>} Expression))/
Sum(DISTINCT {<FY={'2018'}>} Expression) > 1
and Below(Sum(DISTINCT {<FY={'2018'}>} Expression), 2)/
Below(Sum(DISTINCT {<FY={'2018'}>} Expression)) < 1
, Below(Level))
, Type, (LevelNo,(NUMERIC, DESCENDING)))
The only thing I really changed was to use Below(Level) instead of Level. But I also removed the multiple usage of Aggr() as it seems that you were essentially aggregating on the same fields. If Level and levelNo are not one to one for each Type, then you might have to change back to your original calculated dimension and use Below(Level).
Thanks You,It worked