Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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)
7 Replies
Highlighted
Valued Contributor

Re: Sum if or Set Analysis for date minus 1

Hi

Not tested

try this :

=Sum({<Entry_date = {"$(=Date(Today()-1,'MM/DD/YYYY'))"},Doc_type = {'SA'}>} Amount)+

Sum({<Entry_date = {"$(=Date(Today(),'MM/DD/YYYY'))"},Doc_type = {'ZZ'}>} Amount)

Highlighted
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'.

date format YYYY/MM/DD

Highlighted
Not applicable

Re: Sum if or Set Analysis for date minus 1

Thank You. It worked

Partner
Partner

Re: Sum if or Set Analysis for date minus 1

Hi,

you can try:

=Sum({<Entry_date = {"$(=Date(Today()-1,'MM/DD/YYYY'))", Year(max(Entry_date))},Doc_type = {'SA'}>} Amount)+

Sum({<Entry_date = {"$(=Date(Today(),'MM/DD/YYYY'))", Year(max(Entry_date))},Doc_type = {'ZZ'}>} Amount)

Highlighted
Valued Contributor

Re: Sum if or Set Analysis for date minus 1

Sum({< Entry_Date = {'$(=Date(Today()))'}, Doc_Type = {'ZZ'}>}Amt)

+

Sum({< Entry_Date = {'$(=Date(Today()-1))'}, Doc_Type = {'SA'}>}Amt)

Highlighted
Not applicable

Re: Sum if or Set Analysis for date minus 1

For this add DIM_DT as dimension and write expression like below:

=Sum({<Entry_date = {"$(=Date(DIM_DT-1,'MM/DD/YYYY'))"}, Year={"$(=Year(max(Entry_date)))"},Doc_type = {'SA'}>} Amount)+

Sum({<Entry_date = {"$(=Date((DIM_DT,'MM/DD/YYYY'))"}, Year={"$(=Year(max(Entry_date)))"},Doc_type = {'ZZ'}>} Amount)

Highlighted
Honored Contributor III

Re: Sum if or Set Analysis for date minus 1

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!