topic Re: need help on this urgent!! in QlikView

need help on this urgent!!

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: need help on this urgent!!

Sun, 19 Mar 2017 11:41:14 GMT

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???

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 12:36:03 GMT

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

Re: need help on this urgent!!

Sun, 19 Mar 2017 13:11:17 GMT

Hi Anil,

PFB for your reference!!

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 13:14:51 GMT

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

Re: need help on this urgent!!

Sun, 19 Mar 2017 13:20:53 GMT

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

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 13:58:10 GMT

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

Re: need help on this urgent!!

Sun, 19 Mar 2017 14:13:35 GMT

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!!!

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 14:27:26 GMT

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])

Re: need help on this urgent!!

Sun, 19 Mar 2017 14:31:15 GMT

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.

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 14:35:17 GMT

FYI

Re: need help on this urgent!!

Sun, 19 Mar 2017 14:37:54 GMT

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

Please paste your script here

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 15:09:53 GMT

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.

Re: need help on this urgent!!

Sun, 19 Mar 2017 17:14:50 GMT

Anil,

That is fine But If I take my dimensions as

Reqno,Reqdate,Targetdate

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

Re: need help on this urgent!!

Anil_Babu_Samineni — Sun, 19 Mar 2017 17:46:58 GMT

Like this?

Re: need help on this urgent!!

Mon, 20 Mar 2017 02:31:58 GMT

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 — Mon, 20 Mar 2017 04:24:11 GMT

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.