Exclude Future Date not working in the expression.

[saivina2920]

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

[saivina2920]

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]

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]

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

[saivina2920]

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.

[saivina2920]

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

i will share my screen...

[saivina2920]

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]

pls.  find attached some test data. 

[saivina2920]

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