topic Place the value of a field based on its month in QlikView

Place the value of a field based on its month

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to perform the following logic:

Key	Year	Months	Target	Hired in Current Month	Adv Hired in Last Month	Adv Hired in Current Month
1	2018	Jan	10	3	4	3
1	2018	Feb	8	2	3

For Year=2018, Month = Feb, Target=8, if the number of candidates are hired in the same month (Feb), then my count(ID) will be calculated in 'Hired in Current Month' field.

if the candidates join before Feb (if they join in Jan), then i will calculate the count(ID) in 'Adv Hired in Last Month'. And the same Count(ID) value should be reflected in Jan month for 'Adv Hired in Current Month'. If the candidates join in Dec then the value of Feb month 'Adv Hired in Last Month' should be reflected in Dec month 'Advanced Hired in Current Month' field.

Please provide a solution.

Re: Place the value of a field based on its month

datanibbler — Tue, 03 Jul 2018 06:45:04 GMT

Hi Aravind,

well, that should not be a great issue - you do have one line per month and you have the "hiring date" in every line, do you? Then you can, depending on that, generate either the one or the other field.

If you sort the table in a RESIDENT LOAD (using the ORDER BY clause), you can use PEEK() to "look up" the record above to see how what figure is in the field "hired in current month" and put that in the field "hired in prior month".

(use a tablebox on the surface and set the sorting to "LOAD order (original)" on the GUI to check that your sorting is correct and the figures are really calculated as they are supposed to be)

HTH

Best regards,

DataNibbler

Re: Place the value of a field based on its month

Anonymous — Tue, 03 Jul 2018 07:09:37 GMT

Hey Friedrich,

Sorry i couldn't understand your statement. Can you explain me with an example?

Re: Place the value of a field based on its month

qlikviewwizard — Tue, 03 Jul 2018 09:19:15 GMT

Can you provide some more sample data and expected results? It is not clear what you want.

Re: Place the value of a field based on its month

Anonymous — Tue, 03 Jul 2018 09:52:13 GMT

Hi Arjun,

I need to create an expression for 'Adv Hired from from current month' field. It is calculated based on 'adv hired from last month'

This is the logic for 'adv hired from last month' field:

If Candidates ( Candidate refers to the IDs) who need to join by the month of Feb 2018 join before the month of Feb (say Jan according to the below output), then the count of candidates will be calculated for this condition. This whole logic refers to 'Adv hired from Last month'. In terms of expression: (if Month([Joining date])<Month([Transaction_Date]),Sum(Aggr(Count(ID),Key))). Here we calculate the candidates who join before the Transaction date based on a particular key.

The value for 'Adv hired from current month' is obtained based on the Adv hired from last month field. Candidates who joined on Jan instead of Feb according to the transaction date are fallen under the Adv hired from current month field. As you can see the sample output, The Feb month Adv hired from last month field value and Jan month Adv hired from current month field value are equal (Since 10 candidates who should join on Feb have joined on Jan) I need help to create an expression for this field. I have attached the dataset and also the sample output below. Please provide a solution.

Re: Place the value of a field based on its month

imhappiee — Tue, 03 Jul 2018 14:57:16 GMT

I guess you can try with first sorted value function

Anand