topic Re: Showing two measures with the filter selection in QlikView

Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 07:16:55 GMT

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.

Re: Show the two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 09:00:55 GMT

Please help.

Re: Showing two measures with the filter selection

Tue, 02 Jun 2015 09:14:36 GMT

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)

Re: Showing two measures with the filter selection

Tue, 02 Jun 2015 09:18:32 GMT

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

Re: Showing two measures with the filter selection

antoniotiman — Tue, 02 Jun 2015 09:42:00 GMT

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

Re: Showing two measures with the filter selection

jagan — Tue, 02 Jun 2015 10:10:10 GMT

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.

Re: Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 10:11:19 GMT

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

Re: Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 10:40:07 GMT

Hi jagan‌,

I tried with your expression.

Got this output:

Required format:

Re: Showing two measures with the filter selection

antoniotiman — Tue, 02 Jun 2015 10:49:46 GMT

See attachment

Re: Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 11:08:39 GMT

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.

Re: Showing two measures with the filter selection

antoniotiman — Tue, 02 Jun 2015 11:15:04 GMT

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

Why EmpNo E4 -> Name Ename4 ?

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

Re: Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 11:29:45 GMT

Hi AntonioTiman ,

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

Kindly advise.

Re: Showing two measures with the filter selection

antoniotiman — Tue, 02 Jun 2015 11:54:53 GMT

E4 -> Ename4,Ename8,Ename10

Re: Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 11:58:28 GMT

E4 -> Ename4

Re: Showing two measures with the filter selection

antoniotiman — Tue, 02 Jun 2015 12:16:15 GMT

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)

Re: Showing two measures with the filter selection

qlikviewwizard — Tue, 02 Jun 2015 14:17:11 GMT

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

Re: Showing two measures with the filter selection

qlikviewwizard — Wed, 03 Jun 2015 01:09:35 GMT

Hi All,

Any solution? Please.

Re: Showing two measures with the filter selection

jagan — Wed, 03 Jun 2015 05:41:38 GMT

Hi,

PFA for solution.

Regards,

Jagan.

Re: Showing two measures with the filter selection

jagan — Wed, 03 Jun 2015 05:42:26 GMT

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.

Re: Showing two measures with the filter selection

qlikviewwizard — Wed, 03 Jun 2015 15:17:20 GMT

Dear jagan,

You are amazing. Thanks a million.