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.

[#2]

If doj and targetdate both are in the same table then its working fine.

But here am using Left join between the Emp and Inducted table

Then doing again Left join between Requisition and Emp table.

So some extra records are coming!!!

let me know if any wrong with me???

[Anil_Babu_Samineni]

When you talk about you are getting seeing exact results you may share expected result on that set analysis. Would you share qvw please

[#4]

Hi Anil,

PFB for your reference!!

[Anil_Babu_Samineni]

Can you let me know which values you want to see instead of that?

Condition seems works to me If(doj<=targetdate,1,0) as flag1

[#6]

I have attached excel file with my desired results

Headcount is 120

Headcount is 18, If Doj<=targetdate

Headcount is 102, If Doj>targetdate

1st Month count is 38

2nd Month count is 43

3rd month count is 21

Total 38+43+21=102, If Doj>Targetdate

[Anil_Babu_Samineni]

I got these 3

Headcount is 120

Headcount is 18, If Doj<=targetdate

Headcount is 102, If Doj>targetdate

And then, How and which field month you are calculating the 1st, 2nd, 3rd Month

[#8]

Thanks for your replay...

That is what exactly am looking for now we have got head count 102, if Doj>Targetdate

Now I want to show independent counts for every month(38+43+21=102) ,If Doj>Targetdate

i tried this

Creating flags in backend

If(Month(Addmonths(Targetdate,1))=Month(Doj),1,0) as Month1 ,

If(Month(Addmonths(Targetdate,2))=Month(Doj),1,0) as Month2,

If(Month(Addmonths(Targetdate,3))=Month(Doj),1,0) as Month3

Now am using in set analysis but not working properly.

Can you find the logic for this???

I want to know whether its possible or not?

Please suggest.

Request to share your script here!!!

[Anil_Babu_Samineni]

Instead of flag why can't simple set analysis

create an extra field in your script as

num(Month(Yourdatefield)) as Month,

and reload the application and write your expression as

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

[#10]

Anil,

You got 1st three requirements(Total head count,Head count2 if doj<=targetdate,Head count3, if doj>targetdate)

Can you share your script here??

That would be great help if you can share here.