Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:  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. 1 Solution

Accepted Solutions  Luminary Alumni

Hi,

Used this script inside

Data:

*,

Quarter & '-'& Year AS QuarterYear;

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:

Sal AS Incharge1Sal,

QuarterYear

RESIDENT Data

WHERE Len(Trim(Incharge1)) > 0;

Temp:

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.

19 Replies  Master II
Author 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?  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) .... Regards,

Antonio  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.  Master II
Author

I want to show Quarter values... in the report.  Master II
Author

Hi

Required format:   Master III

See attachment  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. 