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

[Anonymous]

Use set analysis. Try this...I would recommend create variable for previous date.

=sum({<Type={XXX}, Date={$(=Max(Date)-1)}>}Amount)

[#3]

Hi Rajan,

what if Date-1 does not have any data?

Kind Regards,

Olle

[Anonymous]

qlikview will ignore the null values and sum all other values

[sunny_talwar]

Why would you want to sum Date-1 if you won't have any data in it?

Best,

S

[#6]

I dont.

For instance today is monday then I want Date-1 to sum up friday.

Best,

Olle

[mukesh24]

Hi,

  To solve these use set analysis

Simply create variable as:-

vMaxYear = max(Year)

vMaxMonth=max({<Year = {"$(vMaxYear)"}>}Month)

vPrevMonth =  month(addmonths(makedate($(vMaxYear), $(vMaxMonth)),-1))

vPrevYear = Year(addmonths(makedate($(vMaxYear), $(vMaxMonth)),-1))

Use above variables in expression using set analysis..

For Current Month selection

sum({<Year = {"$(vMaxYear)"},Month={"$(vMaxMonth)"}>}Amount)

for last month

sum({<Year = {"$(vPrevYear)"},Month={"$(vPrevMonth)"},Year=,Month=>}Amount)

Regards,

Mukesh Chaudhari

[#8]

Getting zero values Im afriad.

Could it be something with my dates?

[sunny_talwar]

You can use a if statement in that case:

vPreviousDate = If(WeekDay(Max(Date)) = 'Mon', Date(Max(Date) - 3), Date(Max(Date) -1)))

=Sum({<Type={XXX}, Date={'$(vPreviousDate)'}>} Amount)

Best,

S

[#10]

hmm, that is a nice looking approach.

But how would I apply it to days rather than months and years?

Best Regards,

Olle