Eliminate rows based on status history

[#1]

Hello All,

CName	Month	Quarter	Status	FEE
INV	Jul	Q3	Active	0
INV	Jul	Q3	Active	0
INV	Jul	Q3	Inactive	250
INV	Aug	Q3	Active	0
INV	Aug	Q3	Active	0
INV	Aug	Q3	Inactive	250
INV	Sep	Q3	Active	0
INV	Sep	Q3	Active	0
INV	Sep	Q3	Inactive	250
INV	Oct	Q4	Inactive	0
INV	Oct	Q4	Inactive	0
INV	Oct	Q4	Inactive	250
CLI	Jul	Q3	Inactive	155
CLI	Aug	Q3	Inactive	155
CLI	Sep	Q3	Inactive	155
CLI	Oct	Q4	Active	0
CLI	Oct	Q4	Inactive	155

I have a requirement to display straight chart with records status as Inactive .

I was able to achieve this using set analysis expression  =Sum({<Status={'Inactive'}>} FEE)

Name	Month	Inactive Fee
		1620
CLI	Aug	155
CLI	Jul	155
CLI	Oct	155
CLI	Sep	155
INV	Aug	250
INV	Jul	250
INV	Oct	250
INV	Sep	250

Now I have another requirement whenever Quarter is selected if anytime status is Active then that record should disappear from above chart

When Q3 is selected INC Cname data should not be displayed since it was active in Q3

When Q4 is selected CLI Cname data should not be displayed since it was active in  Q4

Other records will remain same

Attached is the data sheet and Qv file.

Please help

[Gysbert_Wassenaar]

Perhaps like this =Sum({<Quarter=, Status={'Inactive'}, CName=E({<Status={'Active'}>}CName)>} FEE)

[#3]

Hi Gysbert,

If the above condition is applied by default I would not see any view....

My requirement is by default it should display all the inactive records.

When month or quarter is selected it should check if the cname is active in that particular month or quarter and eliminate those records.

When Jul is selected INV Cname data should not be displayed since it was active in Jul

When Aug is selected INV Cname data should not be displayed since it was active in  Aug

When Sep is selected INV Cname data should not be displayed since it was active in Sep

When Oct is selected CLI Cname data should not be displayed since it was active in  Oct

When Q3 is selected INC Cname data should not be displayed since it was active in Q3

When Q4 is selected CLI Cname data should not be displayed since it was active in  Q4

This condition should work even when multiple months and multiple quarters are selected.

Please help

[sunny_talwar]

See if this expression works for you:

=If(GetSelectedCount(Quarter) = 0 and GetSelectedCount(Month) = 0,

Sum({<Status={'Inactive'}>} FEE),

Sum({<Quarter=, Status={'Inactive'}, CName=E({<Status={'Active'}>}CName)>} FEE))