topic Re: Sum IF, but from previous date with data in QlikView

Sum IF, but from previous date with data

Thu, 19 Feb 2015 14:07:22 GMT

Hi,

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

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

Re: Sum IF, but from previous date with data

Anonymous — Thu, 19 Feb 2015 14:13:18 GMT

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

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

Re: Sum IF, but from previous date with data

Thu, 19 Feb 2015 15:00:09 GMT

Hi Rajan,

what if Date-1 does not have any data?

Kind Regards,

Olle

Re: Sum IF, but from previous date with data

Anonymous — Thu, 19 Feb 2015 15:06:04 GMT

qlikview will ignore the null values and sum all other values

Re: Sum IF, but from previous date with data

sunny_talwar — Thu, 19 Feb 2015 15:14:55 GMT

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

Best,

Re: Sum IF, but from previous date with data

Thu, 19 Feb 2015 15:20:19 GMT

I dont.

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

Best,

Olle

Re: Sum IF, but from previous date with data

mukesh24 — Thu, 19 Feb 2015 15:20:34 GMT

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

Re: Sum IF, but from previous date with data

Thu, 19 Feb 2015 15:22:25 GMT

Getting zero values Im afriad.

Could it be something with my dates?

Re: Sum IF, but from previous date with data

sunny_talwar — Thu, 19 Feb 2015 15:24:25 GMT

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,

Re: Sum IF, but from previous date with data

Thu, 19 Feb 2015 15:25:54 GMT

hmm, that is a nice looking approach.

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

Best Regards,

Olle

Re: Sum IF, but from previous date with data

Thu, 19 Feb 2015 15:30:09 GMT

Getting a Script line error when pasting the vPreviousDate into my script

Re: Sum IF, but from previous date with data

Anonymous — Thu, 19 Feb 2015 15:31:01 GMT

Hey Olle:

I am gonna attend a meeting. My friend SunIndia will try to help you on this if you have still not got the right answer.

Sorry.

Re: Sum IF, but from previous date with data

sunny_talwar — Thu, 19 Feb 2015 15:31:41 GMT

Pasting it in the script? Just add to the variables in the front end using the variable overview

Best,

Re: Sum IF, but from previous date with data

mukesh24 — Thu, 19 Feb 2015 16:35:33 GMT

simply create additional day variable same as month...

Re: Sum IF, but from previous date with data

Fri, 20 Feb 2015 09:19:37 GMT

Hi,

For some reason I cannot get the variable to work properly when adding it into the Variable Overview. Same if I SET it in the script. However the if-function works properly.

So now I've got the below two working formulas, how do I put them together? My set analysis skills are poor, I never seem to learn the syntaxes

TODAYS

=SUM(IF(TYPE='XXX',AMOUNT))

BUT NEED TO USE YESTERDAYS DATE

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

Re: Sum IF, but from previous date with data

sunny_talwar — Fri, 20 Feb 2015 11:23:39 GMT

Please find the attachment for further clarification

vPreviousDateTypeXYZ =If(WeekDay(Max({<Type = {'XYZ'}>}Date)) = 'Mon', Date(Max({<Type = {'XYZ'}>}Date) - 3), Date(Max({<Type = {'XYZ'}>}Date) -1))

vPreviousDateTypZZZ =If(WeekDay(Max({<Type = {'ZZZ'}>}Date)) = 'Mon', Date(Max({<Type = {'ZZZ'}>}Date) - 3), Date(Max({<Type = {'ZZZ'}>}Date) -1))

Text Box Expression: =Sum({<Type={'XYZ'}, Date={'$(vPreviousDateTypeXYZ)'}>} Amount)

=Sum({<Type={'XYZ'}, Date={'$(vPreviousDateTypeZZZ)'}>} Amount)

Here Type XYZ has a max date of 02/16/2015 (which was a Monday) so it picks the value from Friday and type ZZZ has a max date of 02/17/2015 (which was a Tuesday) so it picks the value from Monday.

HTH

Best,

Re: Sum IF, but from previous date with data

daveamz — Fri, 20 Feb 2015 12:23:03 GMT

Hi Olle,

Try this one, it will sums up the amount for the max possible date before today:

Sum({<Type = {'XXX'}, Date = {"$(=Max({<Date={`<$(=Today())`}>}Date))"}>}AMOUNT)

David

Re: Sum IF, but from previous date with data

Fri, 20 Feb 2015 12:44:20 GMT

Thanks for your answer,

still cannot apply to mp app.

Im clearly missing out on something,

here comes an example file, any clue why im getting zero values?

//O

Re: Sum IF, but from previous date with data

sunny_talwar — Fri, 20 Feb 2015 13:14:32 GMT

Are you looking to put that number across all possible dates??

If this is what you are looking to do, then use this expression:

=Sum({<Pmt_Subtype={'Credit line connected payment'}, Date={'$(vPreviousDate)'}>} TOTAL Pmt_amount)

Best,

Re: Sum IF, but from previous date with data

Fri, 20 Feb 2015 13:29:21 GMT

Hi again,

Im expecting vPreviousDate to show the previos date for each row,

For instance:

Red row

vPreviousDate should equal 2014/12/11

Sum PreviousDate should equal -1,009,973,010

Blue row

vPreviousDate should equal 2014/12/12 (since there's a weekend in between)

Sum PreviousDate should equal 2,858,915,949