Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

Showing two measures with the filter selection

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.

Capture.JPG

Thanks in advance.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

19 Replies
qlikviewwizard
Master II
Master II
Author

Please help.

Not applicable

I think, if you remove StartDate condition from expression, it will work...

=aggr(Sum({<Year=, Quarter=, Month=>}  Sal),EmpNo)

=aggr(Sum({<Year=, Quarter=, Month=>}  Comm),EmpNo)

Not applicable

Or do you want it to be displayed separately without adding Q1 and Q2 values?

antoniotiman
Master III
Master III

Hi,

You can remove aggr() in expression and add  TOTAL <Quarter,EmpNo>   in Sum().

However You have many Name,Date,Job related Whit EmpNo. You can use

Calculated Dimensions like Aggr(MinString(Name),EmpNo) ....

Imm1.png

Regards,

Antonio

jagan
Luminary Alumni
Luminary Alumni

Hi,

Your variable name is wrong in the expression

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

Also remove the aggr() in expression.

Regards,

Jagan.

qlikviewwizard
Master II
Master II
Author

I want to show Quarter values... in the report.

qlikviewwizard
Master II
Master II
Author

Hi

Required format:

2.JPG

antoniotiman
Master III
Master III

See attachment

qlikviewwizard
Master II
Master II
Author

Hi AntonioTiman,

For E4 Name is showing as Ename10 and HireDate as 04-Mar-2013.

But data in source is Name is Ename4 and HireDate is 25-Mar-2013.

Please help.