How to calculate blank value along with add new column date and user Input

[saivina2920]

How to calculate blank value along with add new column date and user Input

I already defined <10 days, 10 to 30 Days and >30 Days ==> This is working fine.

But now, i want to calculate blank cell of EMP_RELIEVE_DATE.

For Example, whereever the blank available in the column of EMP_RELIEVE_DATE

then we have to take another column value EMP_RESIGN_DATE + "User Input Field"

EMP_RESIGN_DATE             EMP_RELIEVE_DATE
03/08/2020                              03/08/2020
10/08/2020                                 -
10/08/2020                                 -
15/11/2020                                 -
29/11/2020                                 -
07/12/2020                                 -
08/12/2020                               08/12/2020
09/12/2020                               09/12/2020
10/08/2020                                   -
11/12/2020                                    -
12/12/2020                                   -
13/12/2020                                   -
28/12/2020                               28/12/2020
29/12/2020                                    -
30/12/2020                                     -

If EMP_RELIEVE_DATE is 03/08/2020 ==> It means the calulation already processed and display the count.

If found any blank available in the field of EMP_RELIEVE_DATE,

then "get the user input" value + add in EMP_RESIGN_DATE.
EX :

EMP_RESIGN_DATE EMP_RELIEVE_DATE
03/08/2020 03/08/2020
10/08/2020 - ==> User Input "10" + 10/08/2020 = 20/08/2020 (10 + 10 days)

Like that, all the blank cells have to calculate by the system.

Here, 20/08/2020 is the EMP_RELIEVE_DATE for blank cell. Like that we have to calculate EMP_RELIEVE_DATE blank cell and apply in the exist formula of <10 days, 20 to 30 Days and > 30 Days

NOTE : IF VALUES IS THERE IN EMP_RELIEVE_DATE, then the system should calculate the current one formula already defined in variables.

[Marcos_Ferreira_dos_Santos]

Hi Saivina2920!

Have you solved the problem by yourself ?

Your need is perfectly achievable in Qlikview. Maybe you should only take care whether your field content is Null or Blank  (see https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472  for details about this)

good luck!

[saivina2920]

Thanks for your reply.

But, i am expecting result soon.

Thanks for your help and suggestions.

below is the clear step to understand the requirement.

STEP 1 ) if EMP_RELIEVE_DATE column value contains date, then we have to calculate below

      EMP_RELIEVE_DATE ==> perform the count <10days, 10 to 30 Days, > 30 Days

STEP 2 ) if EMP_RELIEVE_DATE column value is null or blank, then we have to calculate below

      Get the User Input + EMP_RESIGN_DATE ==> perform the count <10days, 10 to 30 Days, > 30 Days

STEP : 1 already performed and working well. The little bit difficult is to get the count of, if if EMP_RELIEVE_DATE column value is null or blank.

i already clearly explained in the last attached image.

If understand the above steps, pls. respond for further to proceed.

Note : pls. find attached qlik and excel file for clear reference.

I am struggling to find the count in blank value along with the conditions. pls. guide us. This is urgent.

[saivina2920]

can anyone help regarding the last reply from me. I am waiting for long time. no one give the proper reply for this post.

[Anil_Babu_Samineni]

Perhaps this

= Count({<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))>=$(=today()-10)"}>*
<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))<='$(=today())'"}>} If(IsNull(EMP_RELIEVE_DATE),ID))

[saivina2920]

Thanks for your reply,

Now, the data is coming correct for if EMP_RELIEVE_DATE is blank and if user enter some input.

At the same time, If user will not be entering nothing in the input field, then the NON Blank of EMP_RELIEVE_DATE Count should appear in the same table.

How to do this...? 

Pls. find latest attached qlik file for reference and reply for the same asap pls.

[Anil_Babu_Samineni]

Try this way

=Sum(Aggr(If(Len($(vUserInput))<>0, Count({<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))>=$(=today()-10)"}>*
<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))<='$(=today())'"}>} ID),
Count({<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))>=$(=today()-10)"}>*
<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))<='$(=today())'"}>} If(IsNull(EMP_RELIEVE_DATE),ID))), EMP_RESIGN_DATE, EMP_RELIEVE_DATE))

[Anil_Babu_Samineni · Accepted solution]

Or

=Sum(Aggr(If(IsNull($(vUserInput)), Count({<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))>=$(=today()-10)"}>*
<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))<='$(=today())'"}>} ID),
Count({<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))>=$(=today()-10)"}>*
<ID={"=ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput))<='$(=today())'"}>} If(IsNull(EMP_RELIEVE_DATE),ID))), EMP_RESIGN_DATE, EMP_RELIEVE_DATE))

[saivina2920]

Getting Error and count not coming properly.

kindly pls. check the attached file.

For details reference (Sample Data-29-DEC-2020 Excel file), I have enclosed the excel data File with this.

[Anil_Babu_Samineni]

What are you trying here?

=dayname(ALT(EMP_RELIEVE_DATE, EMP_RESIGN_DATE+$(vUserInput)))