Calculate # of days without Sunday

[#1]

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.

[sorrakis01]

Hi,

You can use set analysis using -=

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

Regards

[effinty2112]

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

[tresesco]

May be like:

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

PFA

[#5]

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.

[sunny_talwar]

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)

[#7]

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