topic Re: Sum if or Set Analysis for date minus 1 in QlikView

Sum if or Set Analysis for date minus 1

Tue, 19 Jul 2016 12:29:01 GMT

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

sunny_talwar — Tue, 19 Jul 2016 16:54:40 GMT

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

Wed, 20 Jul 2016 07:01:40 GMT

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

amit_saini — Wed, 20 Jul 2016 07:06:08 GMT

Hi,

Try:

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

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

Thanks,
AS

Re: Sum if or Set Analysis for date minus 1

amit_saini — Wed, 20 Jul 2016 07:57:39 GMT

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

Thanks,

Re: Sum if or Set Analysis for date minus 1

amit_saini — Wed, 20 Jul 2016 07:58:34 GMT

Check for more details:

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

Thanks,

Re: Sum if or Set Analysis for date minus 1

sunny_talwar — Wed, 20 Jul 2016 12:24:40 GMT

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

Wed, 20 Jul 2016 12:31:56 GMT

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

sunny_talwar — Wed, 20 Jul 2016 13:57:23 GMT

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

Wed, 20 Jul 2016 14:15:50 GMT

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