Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fmazzarelli
Partner - Creator III
Partner - Creator III

a count value dated yesterday

Hi,

howto to count a value dated Yesterday?

Example:

N°     STATUS          PROGRC          day

1          Closed        10                    monday

2          Closed         6                     tuesday

3          New              72                  wednesday

4          Closed          23                   thursday

5          Closed         1                    friday

6              -                    -                    -

7              .                    -                    -

......

Normally I use (UI) (char bar)

x: day

y: sum({<STATUS = {'Closed'}>} [PROGRC])

now I have to add the value of the previous day

x: day

y: sum({<STATUS = {'Closed'}>} [PROGRC]) + value of previous day if status is Closed

So that

Monday 10 + 0

Tuesday 6 + 10

Wednesday 72

Thursday: 23 + 72

Friday: 1 + 23

Howto calculate it?

4 Replies
swuehl
MVP
MVP

Wednesday is STATUS = New, so why do you consider 72?

You can maybe use chart inter record functions like above() to get the previous day results:

=sum({<STATUS = {'Closed'}>} [PROGRC]) + Above( sum({<STATUS = {'Closed'}>} [PROGRC]) )


edit:

or maybe better

=Rangesum( sum({<STATUS = {'Closed'}>} [PROGRC]) , Above( sum({<STATUS = {'Closed'}>} [PROGRC]) ) )

girirajsinh
Creator III
Creator III

In case if you don't intend to show Wednesday as Stefan asked,

here is another way to achieve what you are looking for.

Set Accumulation to 2 Steps back.

Firs chart without Previous day,

Now after previous day ( Previous Bar  in this case ,to be precise)

fmazzarelli
Partner - Creator III
Partner - Creator III
Author

Hi,

your answered is correct and I would like to add an additional sum?

The last formula I added the field 'Rest (green)

sum({<STATUS = {'New'}>} [PROGRC]) + Above( sum({<STATUS = {'New'}>} [PROGRC]) ) + 'Rest'

PS: MY ORIGINAL FORMULA IS A "COUNT" STATEMENT

Immagine.png

swuehl
MVP
MVP

Seems like you want to accumulate more than 1 day back?

There are several approaches, have a look at

Calculating rolling n-period totals, averages or other aggregations