Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

timpoismans
Contributor III

Sum based on date in set analysis

Hi all

In request of a customer, I'm trying to make a table that lists an amount paid over the course of a few date ranges.
The customer wants to see how much was paid after 1 month, 2 months, 3 months, 6 months,...

I decided to focus on the 1 month. With this working, the rest should be a walk through the park.

Consider the following example data:

       

FilenrDate creationdate actionEnd of range dateCheck if action
in range
text1Amount
GM5756610/01/201114/01/201110/02/2011Tpayment202,75
GM5756810/01/20114/02/201110/02/2011Tpayment30,28
GM5757010/01/201115/02/201110/02/2011Fpayment888,71
GM5757210/01/201125/01/201110/02/2011Tpayment563,62
GM5757310/01/201118/01/201110/02/2011Tpayment55,51
GM5757310/01/201118/01/201110/02/2011Tpayment235,47
GM5757510/01/201127/01/201110/02/2011Tpayment455,93
GM5757810/01/201124/03/201110/02/2011Fpayment122,70
GM5757910/01/201114/04/201110/02/2011Fpayment550,54
GM5758010/01/20115/05/201110/02/2011Fpayment100,05
GM5758010/01/20113/03/201110/02/2011Fpayment182,00
GM5758010/01/20113/02/201110/02/2011Tpayment182,00
GM5758010/01/20115/04/201110/02/2011Fpayment91,00
GM5758010/01/201116/06/201110/02/2011Fpayment22,79
GM5758510/01/201127/01/201110/02/2011Tpayment99,47
GM5758610/01/201124/01/201110/02/2011Tpayment576,61
GM5758610/01/201130/03/201110/02/2011Fpayment47,17
GM5758610/01/20113/02/201110/02/2011Tpayment576,62

   

In the data, we have the Date creation: the date the file was created and the start date of the range.

End of range date: the end date of the range. This is simply Date creation + 1 month.

date action: the field that needs to fall in the range.

The field 'Check if action in range' gives a T(rue) or F(alse) if the field is in range.

Now, lets take Filenr GM57586 for example

The filenr occurs 3 times, of which two fall in the range. Now I want to make a list, for each Filenr, which list the total Amount that falls within that range. For Filenr. GM57586 this would be 576.61 + 576.62 = 1153.23



I tried to achieve this with the following formula, which doesn't work:

Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation]),1)))"}>}Amount)

Example of the desired table:

  

FilenrAmount 1M
GM57566202,75
GM5756830,28
GM57570
GM57572563,62
GM57573290,98
GM57575455,93
GM57578
GM57579
GM57580182,00
GM5758599,47
GM575861153,23

Thanks in advance and kind regards,

Tim

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Sum based on date in set analysis

When nothing is selected, then more than one [Date creation] value is possible, which means that Num(AddMonths([Date creation],1)) is null. You need an aggregation function (like min() or max()). For example:

Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths(Max([Date creation]),1)))"}>}Amount)


This will still track your selections, but will also work when nothing is selected.


However, if you need a row by row comparison of [date] and [Date creation], then the set expression will not work. The set expression is evaluated once for the chart, not row by row.. The options are then

  • Sum(If()) - but this can be slow if the data set is large.
  • Create a derived flag field in the load script where 1 means "date in window". Then you can use a set expression with the flag field in the front end.
6 Replies
MVP
MVP

Re: Sum based on date in set analysis

Your expression looks correct except for an misplaced closing parenthesis - change to:

Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation]),1))"}>}Amount)

Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation],1)))"}>}Amount)

                                                                                                                 ^^^^^



(edited)

MVP
MVP

Re: Sum based on date in set analysis

The inner date() function may be redundant:

Sum({<text1={'payment'}, [date action]={"<=$(=AddMonths([Date creation],1))"}>}Amount)

These expressions assume that all the date fields are proper QV numeric date values, not strings.

timpoismans
Contributor III

Re: Sum based on date in set analysis

Yes, noticed the parenthesis and checked, was an error with copying and scrambling the data.

The fields are QV numeric date values, first thing I checked.

But the only thing I can imagine is that there's something wrong with the formatting of the date fields.

Going to double check it again.

timpoismans
Contributor III

Re: Sum based on date in set analysis

Ok, update on the issue:

I got it working, although not completely.

Fiddled with the formula a bit, eventually ending up to just using Num instead of Date.

Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths([Date creation],1)))"}>}Amount)

The only issue I have at the moment, is that it works when I select one value. When I want to show a list for all my files, the formula returns 0.00 across the board.

Any advice on the matter?

MVP
MVP

Re: Sum based on date in set analysis

When nothing is selected, then more than one [Date creation] value is possible, which means that Num(AddMonths([Date creation],1)) is null. You need an aggregation function (like min() or max()). For example:

Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths(Max([Date creation]),1)))"}>}Amount)


This will still track your selections, but will also work when nothing is selected.


However, if you need a row by row comparison of [date] and [Date creation], then the set expression will not work. The set expression is evaluated once for the chart, not row by row.. The options are then

  • Sum(If()) - but this can be slow if the data set is large.
  • Create a derived flag field in the load script where 1 means "date in window". Then you can use a set expression with the flag field in the front end.
timpoismans
Contributor III

Re: Sum based on date in set analysis

Hey Jonathan,

Even though there's only one [Date creation] per file, the formula is calculated on chart level, so it won't work when nothing is selected, okay.

There's more than a million records, a Sum(If()) doesn't seem like it's the best solution.

Had hoped I wouldn't have to dive in the script as it is not my own. Though seems like that would be the best solution.

Thank you, Jonathan.