Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jbakerstull
Creator
Creator

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

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

jbakerstull
Creator
Creator
Author

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

sunny_talwar

My bad... how about 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]))

sunny_talwar

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

jbakerstull
Creator
Creator
Author

That formula worked.

jbakerstull
Creator
Creator
Author

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