6 Replies Latest reply: Jun 26, 2018 11:06 AM by Jeremiah Bakerstull

Part II: Sum based off If and dates

I'm trying to add an condition of Formula B to Formula A. When I combined formulas A and B, the returned value is 0. The column value correctly identifies columns with a yes values. Basically, I'm trying to create a formula to sum the values of  new households column. Any help would be appreciated.

Created Column (New Household)

=If([Entry Date] = Min(TOTAL <[Client Id]> [Entry Date]),If(Min(TOTAL <[Client Id]> [Entry Date])>='4/1/2018','Yes','-'))

A) Value is 375

= Sum(Aggr(If([Entry Date] = Min(TOTAL <[Client Id]> Floor(Num([Entry Date]))),1,0), [Client Id], [Entry Date]))

B) Value is 255

=Sum(Aggr(If(Min(TOTAL <[Client Id]> Floor(Num( [Entry Date])>='4/1/2018')),1,0), [Client Id], [Entry Date]))

Combined = Return value is 0

=Sum(Aggr(If([Entry Date] = Min(TOTAL <[Client Id]> Floor(Num([Entry Date],

If(Min(TOTAL <[Client Id]> Floor(Num([Entry Date])>='4/1/2018'))))),1,0)),

[Client Id], [Entry Date]))

Thanks

• Re: Part II: Sum based off If and dates

May be this

=Sum(Aggr(If([Entry Date] = Min({<[Entry Date] = {'4/1/2018'}>}TOTAL <[Client Id]> Floor(Num([Entry Date]))),1,0), [Client Id], [Entry Date]))

• Re: Part II: Sum based off If and dates

The return count is 4. I'm expecting a count value of 224. Four count is better than 0. It's a good start.

• Re: Part II: Sum based off If and dates

=Sum(Aggr(If([Entry Date] = Min({<[Entry Date] = {">=4/1/2018"}>}TOTAL <[Client Id]> Floor(Num([Entry Date]))),1,0), [Client Id], [Entry Date]))

• Re: Part II: Sum based off If and dates

Or this

=Sum(Aggr(If([Entry Date] = Min(TOTAL <[Client Id]> Floor(Num([Entry Date]))) and Min(TOTAL <[Client Id]> Floor(Num([Entry Date]))) >= '4/1/2018', 1, 0), [Client Id], [Entry Date]))

• Re: Part II: Sum based off If and dates

That formula worked.

• Re: Part II: Sum based off If and dates

Again as always, thank you very much for you're help. It's much appreciate.