Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jbakerstull
Contributor

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

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

6 Replies

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

jbakerstull
Contributor

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

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

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

jbakerstull
Contributor

Re: Part II: Sum based off If and dates

That formula worked.

jbakerstull
Contributor

Re: Part II: Sum based off If and dates

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

Community Browser