Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
FYI
Sorry Anil am unable to open your file as am using trail version.
Please paste your script here
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.
Anil,
That is fine But If I take my dimensions as
Reqno,Reqdate,Targetdate
Then it is not showing what exactly am looking for.....
Like this?
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!!!
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.