
1. Re: Site Visits type Quarter over Quarter? Need help
Shinichiro Murakami Jul 14, 2016 5:23 PM (in response to Jimmy Barber)There are two "Active" status on same quarter x same color x same site number combination.
Then there are four potential different statuses in above combination.
Anyway, you can pick one of those.
The formula concept is as below.
Overview: Level of Detail Expressions
[Status max]
if {fixed [Site Number],[Color]:MAX(if [Quarter]="Q12015" then [Active] end)}= 0
and
{fixed [Site Number],[Color]:MAX(if [Quarter]="Q12016" then [Active] end)}= 1
then "New Color"
elseif
{fixed [Site Number],[Color]:max(if [Quarter]="Q12015" then [Active] end)}= 1
and
{fixed [Site Number],[Color]:max(if [Quarter]="Q12016" then [Active] end)}= 1
then "Comp Color Visit"
elseif
{fixed [Site Number],[Color]:max(if [Quarter]="Q12015" then [Active] end)}= 1
and
{fixed [Site Number],[Color]:max(if [Quarter]="Q12016" then [Active] end)}= 0
then "Deleted Color"
ELSE
"ERR"
END
[Status min]
if {fixed [Site Number],[Color]:min(if [Quarter]="Q12015" then [Active] end)}= 0
and
{fixed [Site Number],[Color]:min(if [Quarter]="Q12016" then [Active] end)}= 1
then "New Color"
elseif
{fixed [Site Number],[Color]:min(if [Quarter]="Q12015" then [Active] end)}= 1
and
{fixed [Site Number],[Color]:min(if [Quarter]="Q12016" then [Active] end)}= 1
then "Comp Color Visit"
elseif
{fixed [Site Number],[Color]:min(if [Quarter]="Q12015" then [Active] end)}= 1
and
{fixed [Site Number],[Color]:min(if [Quarter]="Q12016" then [Active] end)}= 0
then "Deleted Color"
ELSE
"ERR"
END
Thanks,
Shin
9.3 attached.

Comp Visits test_9.3.twbx 98.9 KB


2. Re: Site Visits type Quarter over Quarter? Need help
Jimmy Barber Jul 15, 2016 9:41 AM (in response to Shinichiro Murakami)This is awesome Shin!
Follow up question: if i were to calculate this by week instead of quarter, would you know a more efficient way other than listing out (like below) every week for all 52 weeks?
if {fixed [Site Number],[Color]:min(if [Week]="012015" then [Active] end)}= 0
and
{fixed [Site Number],[Color]:min(if [Quarter]="012016" then [Active] end)}= 1
then "New Color"

3. Re: Site Visits type Quarter over Quarter? Need help
Shinichiro Murakami Jul 15, 2016 12:38 PM (in response to Jimmy Barber)If you want to show the newest week always, below formula can be dynamic.
if {fixed [Site Number],[Color]:min(if [Week] [Quarter]={fixed : max( [Week] [Quarter])} then [Active] end)}= 0
and
{fixed [Site Number],[Color]:min(if [Quarter]="012016" then [Active] end)}= 1
then "New Color" end
Thanks,
Shin