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: 
fgirardin
Creator
Creator

Sum sales between specific dates

Hello !

I'm stuck trying to displaying the sum of money that are due from our clients and not yet paid, but for specific dates

Every Week, 1 person has to export a list from our ERP to Excel.

This file is then used in QlikView (charts, ...)

The tables are

CLIENT        DOCUMENT NBR          DUE DATE          AMOUNT

A                    12345-1                        03.04.2018              50.-

A                    14578-2                        07.05.2018            100.-

B                    25478-7                        04.04.2018            100.-

C                    58485-1                       09.05.2018               50.-   

TOTAL                                                               300.-

I'd like to show the total amount due for each client, only if the DUE DATE is <=Date(Today())

If i use today's date as example 05.04.2018 (DD.MM.YYYY), my table should look like this:

CLIENT        DOCUMENT NBR          DUE DATE          AMOUNT

A                    12345-1                        03.04.2018              50.-

TOTAL A                                                                            50.-

B                    25478-7                        04.04.2018            100.-

TOTAL  B                                                                          100.-

TOTAL:                                                              150.-


Actually, I'd like to make the simplest table >


CLIENT                  AMOUNT

A                                  50.-

TOTAL A                      50.-

B                                100.-

TOTAL  B                  100.-

TOTAL:                150.-

I tried the following expression

=if(DUE_DATE <= Date(Today()), sum(AMOUNT))

Here's what my table should look like (using manual date selection)

cap_qv001.JPG

and this is what it looks like if I use my expression

cap_qv002.JPG

As you can see, it seems to be working as some values are correct (103.90, 129.60, 540.10, ...)

but if I have more than 1 DUE_DATE for a CLIENT, it does not show me the total amount

Every Client that don't show have multiple dates and amount

As you can see here, the total is not displayed if I use another table

cap_qv003.JPG

Thank you for your help !

FG

1 Solution

Accepted Solutions
Digvijay_Singh

You may try this -

=Sum(if(DUE_DATE <= Date(Today()), AMOUNT))

View solution in original post

6 Replies
Anonymous
Not applicable

I suggest making a custom dimension that is every date that is past due.
Then check show subtotals in presentation tab.
edit----
it seems that your sales expression is a dimension.
Try sum(AMOUNT) even though it's 1-1 granular in expression instead of just the dimension name

Digvijay_Singh

You may try this -

=Sum(if(DUE_DATE <= Date(Today()), AMOUNT))

Digvijay_Singh

Or may be using set expression -

Sum({<DUE_DATE={"<=$(=Date(Today()))"}>}AMOUNT)

sasiparupudi1
Master III
Master III

May be try

Sum({<DUE_DATE={"<=$(=Date(toDay(),'DD.MM.YYYY'))"}>} AMOUNT)

sasiparupudi1
Master III
Master III

If the expression does not work, pl provide a sample

fgirardin
Creator
Creator
Author

Sorry for my late reply !

You solution works, Thank you a lot Digvijay !!

Best wishes