topic Re: Exclude Future Date not working in the expression. in QlikView

Exclude Future Date not working in the expression.

saivina2920 — Sat, 16 Nov 2024 17:35:58 GMT

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

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 02:01:49 GMT

can anyone give us the solution for this...? This is urgent pls.

Re: Exclude Future Date not working in the expression.

MayilVahanan — Wed, 20 Jan 2021 02:26:43 GMT

Hi @saivina2920

You can try like below in first expression

If(([1-10 Days]+[10-30 Days]+[ >30 Days]) > 0, 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)))

-- ([1-10 Days]+[10-30 Days]+[ >30 Days]) are column names of exp2,3 & 4 resp.

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 03:12:19 GMT

Thanks for your help.

if i use the condition "If(([1-10 Days]+[10-30 Days]+[ >30 Days]) > 0", i am getting blank date in "Relieving_Date" column. so count shows always zero.

Enclosed the sample file for your reference.

I used below logic to form the count and exact value based on the user input. but, we are getting many error.

pls. guide me/correct me to get the correct count.

My main objective is,

if user enter input, then "Relieving_Date" field should calculate Date(EMP_RESIGN_DATE+vUserInput) along with Date(EMP_RELIEVE_DATE)

if user will not be enter anything, then "Relieving_Date" field should calculate Date(EMP_RELIEVE_DATE).

Re: Exclude Future Date not working in the expression.

MayilVahanan — Wed, 20 Jan 2021 03:43:39 GMT

Hi @saivina2920

May be, try like attachment

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 05:13:55 GMT

Awesome and getting correct count.

Is it possible to same count (1-10days,10 to30 Days, >30 Days) in pie chart with legend...?

If this pie chart has coming proper count with legend, then my long pending task is over.

I tried, but, not coming properly.

Note : pls. use same attachment for pie chart modify (pie chart already available in the attachment. but, not working condition)

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 05:18:29 GMT

pls. give us the clue for pie chart legend which is formed and same in chart straight table..

This is required..

Re: Exclude Future Date not working in the expression.

MayilVahanan — Wed, 20 Jan 2021 06:27:30 GMT

Hi @saivina2920

Hope attachment will give some idea to you.

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 07:23:57 GMT

Really you great. I implemented as per my requirement.

It's working almost cases.

But, some case showing difference. How it is possible and why this?

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 10:23:14 GMT

Can you reply for the same..

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 19:11:03 GMT

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

Re: Exclude Future Date not working in the expression.

saivina2920 — Wed, 20 Jan 2021 19:11:24 GMT

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

Re: Exclude Future Date not working in the expression.

QFabian — Wed, 20 Jan 2021 19:19:24 GMT

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

Re: Exclude Future Date not working in the expression.

saivina2920 — Thu, 21 Jan 2021 02:25:54 GMT

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

Re: Exclude Future Date not working in the expression.

MayilVahanan — Thu, 21 Jan 2021 04:03:50 GMT

Hi @saivina2920

Without seeing the scenario, its difficult to proceed further.

Re: Exclude Future Date not working in the expression.

saivina2920 — Thu, 21 Jan 2021 04:55:07 GMT

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

i will share my screen...

Re: Exclude Future Date not working in the expression.

saivina2920 — Thu, 21 Jan 2021 05:09:04 GMT

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)

Re: Exclude Future Date not working in the expression.

saivina2920 — Thu, 21 Jan 2021 05:29:01 GMT

pls. find attached some test data.

Re: Exclude Future Date not working in the expression.

saivina2920 — Thu, 21 Jan 2021 08:11:19 GMT

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

Dimesnions

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

Re: Exclude Future Date not working in the expression.

MayilVahanan — Thu, 21 Jan 2021 10:44:09 GMT

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