Solved: Re: Showing two measures with the filter selection - Qlik Community

qlikviewwizard · ‎2015-06-02

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.

jagan · ‎2015-06-03

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

qlikviewwizard · ‎2015-06-02

Please help.

Report Inappropriate Content · ‎2015-06-02

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)

Report Inappropriate Content · ‎2015-06-02

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

antoniotiman · ‎2015-06-02

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

Regards,

Antonio

jagan · ‎2015-06-02

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 · ‎2015-06-02

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

qlikviewwizard · ‎2015-06-02

Hi jagan‌,

I tried with your expression.

Got this output:

Required format:

antoniotiman · ‎2015-06-02

See attachment

qlikviewwizard · ‎2015-06-02

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.