Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
Creator
Creator

Exclude Future Date not working in the expression.

I am using Chart Straight table.

I have used condition to exclude future date.

But, still the future date is appearing. How to exclude the future date. below is my dim/expressions.

Dimensions

=If(vUserInput, if(IsNull(EMP_RELIEVE_DATE) AND [EMP_STATE]='$(vEmPlaces)', date(EMP_RESIGN_DATE),date(EMP_RELIEVE_DATE)), if(not IsNull(EMP_RELIEVE_DATE) AND EMP_RELIEVE_DATE <= floor(Today()) AND [EMP_STATE]='$(vEmPlaces)', EMP_RELIEVE_DATE))

Exp1 : "Relieving_Date" (LABEL TEXT)

=If(vUserInput, if(IsNull([EMP_RELIEVE_DATE]),Date(EMP_RESIGN_DATE+vUserInput),Date(EMP_RELIEVE_DATE)),if(not IsNull([EMP_RELIEVE_DATE]) AND EMP_RELIEVE_DATE <= floor(Today()) AND [EMP_STATE]='$(vEmPlaces)', EMP_RELIEVE_DATE))

Exp2 : 1-10 Days

if([Relieving_Date]>=vCalDate10 And [Relieving_Date]<=vToday AND [Relieving_Date] <= Today() AND [EMP_STATE]='$(vEmPlaces)',1,0)

Exp3: 10-30 Days

If([Relieving_Date]>=vCalDate30 And [Relieving_Date]<=vCalDate10 AND [Relieving_Date] <= Today() AND [EMP_STATE]='$(vEmPlaces)', 1,0)

Exp3: >30 Days

If([Relieving_Date]<=vCalDate30 AND [Relieving_Date] <= Today() AND [EMP_STATE]='$(vEmPlaces)', 1,0)

Also the main important points, if all three cases (1-10 Days, 10-30 Days, >30 Days) "0" values, we just want to omit the Rows. this is important.

How to change the above dim/expressions to Omit the rows if all three cases "0" values...?

Futrure Date - 19JAN2021-1.PNG

 

 

Labels (2)
20 Replies
saivina2920
Creator
Creator
Author

Dear mayil vahanan,

some records coming correct in straight table. and pie chart.

but, very few records coming mismatch in straight table. and pie chart.

The data's from database is huge. I do know how to check the exact problem.

What could be the problem and how to solve this..

Count mismatch occur in Live data... that's why I am worried..

saivina2920
Creator
Creator
Author

Dear mayil vahanan,

some records coming correct in straight table. and pie chart.

but, very few records coming mismatch in straight table. and pie chart.

The data's from database is huge. I do know how to check the exact problem.

What could be the problem and how to solve this..

Count mismatch occur in Live data... that's why I am worried..

QFabian
Specialist III
Specialist III

Hi @saivina2920 , create another field based on Relieving_Date in your script, and use the new one in chart :

if (Relieving_Date > today(), null(), Relieving_Date) as Relieving_Date2

QFabian
saivina2920
Creator
Creator
Author

Hi QFabian, where i have to use as mentioned below condition..

if (Relieving_Date > today(), null(), Relieving_Date) as Relieving_Date2 ....?

Either create new expression, or shall i use in existing expression...?

pls. reply for the same..

Note : we already used 3 Expressions in chart (1-10 days,10-30 days,>30days) and "vRelivingDate" is a variable.

vRelivingDate ==> If(vUserInput, if(IsNull([EMP_RELIEVE_DATE]) AND [EMP_STATE]='$(vEmPlaces)',Date(EMP_RESIGN_DATE+vUserInput),Date(EMP_RELIEVE_DATE)),if(not IsNull([EMP_RELIEVE_DATE]) AND EMP_RELIEVE_DATE <= floor(Today()) AND [EMP_STATE]='$(vEmPlaces)', EMP_RELIEVE_DATE))

Chart Expressions below

Exp1 : 1-10 days

Sum(Aggr(if($(vRelivingDate)>=vCalDate10 And $(vRelivingDate)<=vToday AND $(vRelivingDate) <= Today() AND [EMP_STATE]='$(vEmPlaces)',1,0), DOC_NO, EMP_RESIGN_DATE))

Exp2 : 10-20 days

Sum(Aggr(If($(vRelivingDate)>=vCalDate30 And $(vRelivingDate)<=vCalDate10 AND $(vRelivingDate) <= Today() AND [EMP_STATE]='$(vEmPlaces)', 1,0), DOC_NO, EMP_RESIGN_DATE))

Exp3:  >30 days

Sum(Aggr(If($(vRelivingDate)<=vCalDate30 AND $(vRelivingDate) <= Today() AND [EMP_STATE]='$(vEmPlaces)', 1,0), DOC_NO, EMP_RESIGN_DATE))

MayilVahanan

Hi @saivina2920 

Without seeing the scenario, its difficult to proceed further.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
saivina2920
Creator
Creator
Author

Is it possible to contact through phone or chat...?

i will share my screen...

saivina2920
Creator
Creator
Author

I am using the same formula in pie chart which is used in chart straight table.

Only difference we used doc_no and sentdate in sum of aggr.

may be some relieving_date null might not be affected while sum of aggr...???? (not sure)

saivina2920
Creator
Creator
Author

pls.  find attached some test data. 

saivina2920
Creator
Creator
Author

In straight table, we used below is the conditions in dimensions.

Dimesnions

=If(vUserInput, if(IsNull(EMP_RELIEVE_DATE) AND [EMP_STATE]='$(vEmPlaces)', date(EMP_RESIGN_DATE),date(EMP_RELIEVE_DATE)), if(not IsNull(EMP_RELIEVE_DATE) AND EMP_RELIEVE_DATE <= floor(Today()) AND [EMP_STATE]='$(vEmPlaces)', EMP_RELIEVE_DATE))

In Piechart table, 

we haven't used any  user input conditions. we just used sum of aggr and apply condition expression for                1-10days,20 to 30 days and >30 days.

Anything want to like add the above vUserInput condition in sum of aggr...?

MayilVahanan

Hi @saivina2920 

May be, try like below

Sum(Aggr(If($(vRelivingDate)<=vCalDate30 AND $(vRelivingDate) <= Today() AND [EMP_STATE]='$(vEmPlaces)', 1,0), DOC_NO, EMP_RESIGN_DATE,
$(=$(vDim))))

vDim:

(If(vUserInput, if(IsNull(EMP_RELIEVE_DATE) AND [EMP_STATE]='$(vEmPlaces)', 'EMP_RESIGN_DATE','EMP_RELIEVE_DATE'), if(not IsNull(EMP_RELIEVE_DATE) AND EMP_RELIEVE_DATE <= floor(Today()) AND [EMP_STATE]='$(vEmPlaces)', 'EMP_RELIEVE_DATE')))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.