Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
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

7 Replies
galax_allu
Specialist
Specialist

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)

Not applicable
Author

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

Not applicable
Author

Thank You. It worked

thabang_petje
Partner - Creator III
Partner - Creator III

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)

tyagishaila
Specialist
Specialist

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

+

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

Not applicable
Author

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)

oknotsen
Master III
Master III

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!