9 Replies Latest reply: Jul 20, 2016 10:15 AM by Ruan Ramon Silva Diad

# Sum if or Set Analysis for date minus 1

Good day

Please assist. I am looking for an expression that will sum today's Doc_type = 'ZZ' with yesterday's Doc_type = 'SA'.

There is an Entry_date field and I am summing an Amount field.

Please assist with a set analysis or Sum if statement

• ###### Re: Sum if or Set Analysis for date minus 1

May be this:

Sum({<Doc_type = {'ZZ'}, Entry_date = {"\$(=Date(Today(), 'DateFieldFormatHere'))"}>+<Doc_type = {'SA'}, Entry_date = {"\$(=Date(Today() - 1, 'DateFieldFormatHere'))"}>}Amount)

Here you need to replace DateFieldFormatHere with your Entry_date field's format (eg. D/M/YYYY or M/D/YYYY or however way it looks)

• ###### Re: Sum if or Set Analysis for date minus 1

Hi

What is the formula in a case where the sum is not limited to today and yesterday but for all history data for the year. With the same conditions.

Sum amount of a days(Entry_date) Doc_type = 'ZZ' with the previous days(Entry_date) Doc_type = 'SA'.

dateformat YYYY/MM/DD

• ###### Re: Sum if or Set Analysis for date minus 1

sum({<Doc_type = {'ZZ','SA'},Year=,Month=,Entry_date]=>} Amount)

Thanks,

AS

• ###### Re: Sum if or Set Analysis for date minus 1

Check for more details:

https://community.qlik.com/docs/DOC-6163

Thanks,

AS

• ###### Re: Sum if or Set Analysis for date minus 1

What is the formula in a case where the sum is not limited to today and yesterday but for all history data for the year. With the same conditions.

Sum amount of a days(Entry_date) Doc_type = 'ZZ' with the previous days(Entry_date) Doc_type = 'SA'.

The statements in red seems to be contradicting each other. not limited to today and yesterday vs previous day? Can you elaborate?

• ###### Re: Sum if or Set Analysis for date minus 1

I thought today() is limited to current day. But it worked for the whole report.

• ###### Re: Sum if or Set Analysis for date minus 1

But it worked for the whole report? Again I am not sure I understand what you are implying

• ###### Re: Sum if or Set Analysis for date minus 1

Hi,

Do you want to sum YTD to ZZ and YTD - 1 to SA?

example:

ZZ 01/01/2016 - 20/07/2016;
SA 01/01/2016 - 19/07/2016

or max day of selected date?

Example: If i select june 2016:

ZZ 30/06/2016

SA 29/06/2016

• ###### Re: Sum if or Set Analysis for date minus 1

Hi,

Try:

sum({<Doc_type = {'ZZ'},Entry_date]={'\$(=Date(today()-1))'}>} Amount)

+ sum({<Doc_type = {'ZZ'},Entry_date]={'\$(=Date(today()))'}>} Amount)

Thanks,
AS