topic Re: How to write expression for below requirement in QlikView

How to write expression for below requirement

kunkumnaveen — Thu, 21 Feb 2019 13:54:13 GMT

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

Re: How to write expression for below requirement

sunny_talwar — Thu, 21 Feb 2019 14:05:21 GMT

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?

Re: How to write expression for below requirement

kunkumnaveen — Fri, 22 Feb 2019 06:16:48 GMT

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)

Re: How to write expression for below requirement

kunkumnaveen — Fri, 22 Feb 2019 06:17:22 GMT

Re: How to write expression for below requirement

kunkumnaveen — Fri, 22 Feb 2019 10:04:12 GMT

Hello any suggestion ,on above requirement

Re: How to write expression for below requirement

sunny_talwar — Fri, 22 Feb 2019 12:14:24 GMT

Checking it now

Re: How to write expression for below requirement

sunny_talwar — Fri, 22 Feb 2019 12:27:20 GMT

@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).

Re: How to write expression for below requirement

kunkumnaveen — Mon, 25 Feb 2019 03:53:46 GMT

Thanks You,It worked