Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Tags (1)
9 Replies

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)

Not applicable

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

amit_saini
Honored Contributor III

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

amit_saini
Honored Contributor III

Re: Sum if or Set Analysis for date minus 1

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

Thanks,

AS

amit_saini
Honored Contributor III

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?

Not applicable

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

Not applicable

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

Community Browser