Sum IF, but from previous date with data

[#1]

Hi,

I need to sum like below but for the previous applicable date, any ideas?

=SUM(IF(Type='XXXX',Amount))

[daveamz]

Like this?

[#3]

Closing in David!

But not quite as simple as that.

For instance, 29-Nov was a Saturday.

In other words the amount From friday (-49,071,970.70) should not show in the second column until monday

1-Dec

[sunny_talwar]

Is this what you want? PFA

NewExpression:

Sum Previous Date = Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)))

Previous Date = Above(Date)

HTH

Best,

S

[#5]

Im expecting the below result.

I removed the column showing prev date, just confusing us i think.

[sunny_talwar]

This is what you want? PFA

Expressions:

Sum Previous Date:

=If(Match(WeekDay(Date), 'Tue', 'Wed', 'Thu', 'Fri'), Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount))),

If(WeekDay(Date) = 'Mon', Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3)))

PreviousDate:

=If(Match(WeekDay(Date), 'Tue', 'Wed', 'Thu', 'Fri'), Above(Date),

If(WeekDay(Date) = 'Mon', Above(Date,3)))

Please let me know what you think.

Best,

S

[#7]

Haha, we are closing in indeed!

However, just realized, a weekday based solution will not work for public holidays (holidays in the middle of the week) as you can see below.

"Date" column 25-Jan, "Sum prev date" should be showing 0

and 38,712,283 should be showing two rows below ("Date" column 29-Jan). You see what I mean?

[sunny_talwar]

Hahahaha you are complicating matters now. Is it safe to assume that any day with 0 pmt_amount will be weekday or holiday??

Best,

S

[#9]

haha, yes that is correct.

If no pmt_amounts (for any sub type) then that is a holiday/weekend for sure

//O

[sunny_talwar]

I am trying to work on it, but with my limited knowledge of QlikView, I am unable to think of a way to do this. While I am still going to work on this, I hope that someone else (more knowledgeable than me) comes in and resolve the issue for you.

Best,

S

[sunny_talwar · Accepted solution]

Does this looks right. But this will need to be configured even more in case there are more than 4 holidays (weekend +holidays) in a row.

Expression:

=If(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0, If(Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),4) = 0,

If(Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3) = 0, If(Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0,

If(Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0, Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),5),

Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)),

Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), If(Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0 and

Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0,

Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3), Above(sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1))))

Best,

S