Showing two measures with the filter selection

[qlikviewwizard]

Hi All,

We have the hierarchy EmpNo,Incharge2,Incharge1.

Report should display EmpNo,dimesions with Sal,Comm.

Used the below expression for Dynamic two measures display with the filter selections.

=aggr(Sum({<Year=, Quarter=, Month=,StartDate={'>=$(=vStartDate)<=$(=vMaxDate)'}>}  Sal),EmpNo)

=aggr(Sum({<Year=, Quarter=, Month=,StartDate={'>=$(=vStartDate)<=$(=vMaxDate)'}>}  Comm),EmpNo)

I am unable to do this scenario:

If I select Q2, It should display Q1 Sal,Comm and Q2 Sal,Comm. How to achieve this.

Please find the attached file.

Thanks in advance.

[antoniotiman]

EmpNo E4 is related whit many Name (Ename4,Ename10,....)

Why EmpNo E4 -> Name Ename4 ?

In Calculated Dimension You change Min/MaxString with Your need.

[qlikviewwizard]

Hi AntonioTiman ,

I have decoded the data actually.EmpNo E4 -->>> Name is Ename4.

Kindly advise.

[antoniotiman]

E4 -> Ename4,Ename8,Ename10

[qlikviewwizard]

E4 -> Ename4

[antoniotiman]

Why E4 -> Ename4 and Why Not E4 -> Ename10 ?

However, try to change Calculated Dimension with

=Aggr(MinString(Left(Name,5))&Min(Num(Mid(Name,6,2))),EmpNo)

[qlikviewwizard]

Hi  AntonioTiman, This working for this one but not working for other data.

[qlikviewwizard]

Hi All,

Any solution? Please.

[jagan]

Hi,

PFA for solution.

Regards,

Jagan.

[jagan · Accepted solution]

Hi,

Used this script inside

Data:

LOAD

*,

Quarter & '-'& Year AS QuarterYear;

LOAD EmpNo,

     Name,

     Department,

     Job,

     ID,

     Incharge2,

     Incharge2Name,

     Incharge1,

     Incharge1Name,

     Sal,

     Date(HireDate) as HireDate,

     Date(StartDate) as StartDate,

     Month(Date(StartDate)) as Month,

     'Q'&Ceil(month(Date(StartDate))/3) as Quarter,

     Year(Date(StartDate)) as Year,

     Date(LastDate) as LastDate

FROM

[A - Copy (1).xlsx]

(ooxml, embedded labels, table is Employee);

Temp:

LOAD Incharge1 AS EmpNo,   

     Sal AS Incharge1Sal,

     QuarterYear    

RESIDENT Data

WHERE Len(Trim(Incharge1)) > 0;

Temp:

LOAD Incharge2 AS EmpNo,   

     Sal AS Incharge2Sal,

     QuarterYear    

RESIDENT Data

WHERE Len(Trim(Incharge2)) > 0;

and used below expression

=Sum({<Year=, Quarter=, Month=,StartDate={'>=$(=vStartDate)<=$(=vMaxDate)'}>}  Sal) +

Sum({<Year=, Quarter=, Month=,StartDate={'>=$(=vStartDate)<=$(=vMaxDate)'}>}  Incharge1Sal) +

Sum({<Year=, Quarter=, Month=,StartDate={'>=$(=vStartDate)<=$(=vMaxDate)'}>}  Incharge2Sal)

Hope this helps you.

Regards,

Jagan.

[qlikviewwizard]

Dear jagan,

You are amazing. Thanks a million.