need help on this urgent!!

[#1]

Hi Viewers,

This requirement which is based on requisition details.

I have Emp table and it contains

empno,

doj

and Requisition table and it contains

reqno,

reqdate,

targetdate,

no of emps

and also am attaching 1more table(Induction)

it contains

reqno,

empno,

Now my Requirement is take dimensions as reqno,reqdate,targetdate

>Calculate Headcount (Its working fine)

Here I got totally strucked.

>Total emp headcount(which is Doj<=targetdate)

>If Doj>Targetdate, then saparate 1st month emp   count

>If Doj>Targetdate, then saparate 2nd month emp count

If Doj>Targetdate, then saparate 3rd month emp count

Note:Doj>Targetdate which is equilent to

1stmonth+2nd month+3rdmonth.....

To calculate only 1stmonth count when Doj>Targetdate

am using if condition as follows as below

If(Month(AddMonths(targetdate,1))=Month(Doj),1,0) as flag1

Now in the front end as using Set expression Sum({<flag1={1}>}no of emps) 

But am not getting accurate results what exactly am looking for.

PFB for your reference.

Hope you understand my requirement.

Any help will be appreciated.

[Anil_Babu_Samineni]

FYI

[#3]

Sorry Anil am unable to open your file as am using trail version.

Please paste your script here

[Anil_Babu_Samineni]

I've nothing done. Simply loaded and used Rowno() for common to get it

LOAD Rowno() as ID,empno,

    doj

FROM

(ooxml, embedded labels, table is Sheet1);

Sample1:

LOAD Rowno() as ID,reqno,

    reqdate,

    targetdate,

    [no of emps]

FROM

(ooxml, embedded labels, table is Sheet2);

LOAD Rowno() as ID,reqno,

    empno,

    status

FROM

(ooxml, embedded labels, table is INDUCTED);

Front-end I've taken straight table with the dim as doj, targetdate, empno

And expressions are

1) HC ---> Sum([no of emps])

2) <= ----> If(doj<=targetdate,sum([no of emps]))

3) > ------> If(doj>targetdate,sum([no of emps]))

I would highly request you to please mark helpful if it suits for you.

[#5]

Anil,

That is fine But If I take my dimensions as

Reqno,Reqdate,Targetdate

Then it is not showing what exactly am looking for.....

[Anil_Babu_Samineni]

Like this?

[#7]

Yes Anil,

Why am not getting same results????

Anything wrong with me?

and also I tried your expression as you suggested the expression for individual months head count

sum({$<Yourdatefield = {'$(=Min(Month))'} [no of emps])

Please suggest where I did wrong!!!

Re: need help on this urgent!!

[Anil_Babu_Samineni]

1) You have condition with doj. Try to take doj and hide that column from Presentation tab.

2) sum({$<Yourdatefield = {'$(=Min(Month))'} [no of emps])

Note: You may forget to place your Date field instead of Yourdatefield. You should use targetdate on this.