topic Re: Calculate # of days without Sunday in QlikView

Calculate # of days without Sunday

Wed, 25 Nov 2020 16:16:04 GMT

Date Day Value

05-05-2016 Thu 10

06-05-2016 Fri 8

07-05-2016 Sat -5

08-05-2016 Sun -5

09-05-2016 Mon -2

10-05-2016 Tue -3

11-05-2016 Wed -7

12-05-2016 Thu -7

Give the sample table above, i have a requirement to calculate the number of days between the Date which has the last occurrence of a positive value and max(Date), if max(Date) value is negative,excluding Sunday

i.e number of days between 06-05-2016(as this is the date with last occurrence of positive value) and 12-05-2016( which is max(Date) and has a negative value), excluding Sunday(08-05-2016)

Any guidance on how to go about this would be much helpful.

Re: Calculate # of days without Sunday

sorrakis01 — Tue, 14 Jun 2016 10:33:34 GMT

Hi,

You can use set analysis using -=

Sum({$<Day -={'Sun'}>}Value)

Regards

Re: Calculate # of days without Sunday

effinty2112 — Tue, 14 Jun 2016 10:59:40 GMT

Hi,

Try this

=Count({$<Date = {">=$(=Max({$<Value = {">0"}>}Date))"}, Day -= {'Sun'}>}Date)

will return 6 including the latest date with +ve value and the last date.

If you want to exclude the latest date with +ve value and the last date then this

=Count({$<Date = {">$(=Max({$<Value = {">0"}>}Date))"}, Day -= {'Sun'}>}Date)-1

which returns 4.

Cheers

Andrew

Re: Calculate # of days without Sunday

tresB — Tue, 14 Jun 2016 11:02:12 GMT

May be like:

=Count({<Day-={Sun},Date={'<=$(=Date(Max({<Value={"=Sign(Value)=-1"}>}Date))) >=$(Date(Max({<Value={"=Sign(Value)=1"}>}Date)))'}>} Date)

PFA

Re: Calculate # of days without Sunday

Thu, 16 Jun 2016 01:59:39 GMT

Hi ,

it kinds helps but im stuck in another scenario.

ID Name Date Day Value

1 A 05-05-2016 Thu 10

1 A 06-05-2016 Fri 8

1 A 07-05-2016 Sat -5

1 A 08-05-2016 Sun -5

1 A 09-05-2016 Mon -2

1 A 10-05-2016 Tue -3

1 A 11-05-2016 Wed -7

1 A 12-05-2016 Thu -7

1 B 09-05-2016 Thu 11

1 B 10-05-2016 Fri 4

1 B 11-05-2016 Sat -3

1 B 12-05-2016 Sun -1

When i put the above table as pivot table and add this expression

=Count({$<Date = {">=$(=Max({$<Value = {">0"}>}Date))"}, Day -= {'Sun'}>}Date)

Date = {">=$(=Max({$<Value = {">0"}>}Date))"} always takes value as 10-05-2016 (max between 06-05-2016 and 10-05-2016) and calculates, instead of calculating individually for ID 1, Name A with 06-05-2016 and ID 1 Name B with 10-05-2016 as dates with last positive value

Kindly share ur inputs.

Re: Calculate # of days without Sunday

sunny_talwar — Thu, 16 Jun 2016 04:13:13 GMT

Set analysis is evaluated once per chart, you might need to use Aggr() to achieve what you are looking to do.

Count({<Day -= {'Sun'}>} If(Date >= Max(TOTAL <ID, Name> {$<Value = {">0"}>} Aggr(Date, ID, Name)), Date)

Re: Calculate # of days without Sunday

Thu, 16 Jun 2016 04:53:49 GMT

Thank you for your response. i tried to use that expression in my table and it shows error in expression, even after closing the parenthesis for count in the end.

Count({<Day -= {'Sun'}>} If(Date >= Max(TOTAL <ID, Name> {$<Value = {">0"}>} Aggr(Date, ID, Name)), Date))