Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
You can use set analysis using -=
Sum({$<Day -={'Sun'}>}Value)
Regards
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
May be like:
=Count({<Day-={Sun},Date={'<=$(=Date(Max({<Value={"=Sign(Value)=-1"}>}Date))) >=$(Date(Max({<Value={"=Sign(Value)=1"}>}Date)))'}>} Date)
PFA
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.
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)
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))