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

Sum if or Set Analysis for date minus 1

Wed, 25 Nov 2020 16:16:04 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

Anonymous — Tue, 19 Jul 2016 15:43:33 GMT

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)

Wed, 20 Jul 2016 06:50:12 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'.

date format YYYY/MM/DD

Wed, 20 Jul 2016 11:38:50 GMT

Thank You. It worked

Anonymous — Wed, 20 Jul 2016 12:14:54 GMT

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 — Wed, 20 Jul 2016 12:27:28 GMT

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

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

Wed, 20 Jul 2016 12:39:04 GMT

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 — Wed, 20 Jul 2016 13:46:21 GMT

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 .