Sum quantity earlier than the selected date

[jianghong]

Dear experts, I am new to Qlik and cannot find methods to do the following calculation.

I have a date filter, and a bar chart of quantity. Now I simply want to sum the quantity earlier than the selected date. Tried the set analysis something like 

sum({$ <date = {$(<=Max(date) ))}>} ,quantity)

sum(${$<=[date] = {$(=max([date]))>},[quantity])

but doesn't work.

Could you please advise the solution. Thanks for your help.

[Rodj]

You are pretty close, try this:

sum({$ <date = {"$(<=Max(date))"}> } quantity)

You don't need the comma, but you do need the quotes as what you are using is a set modifier with an advanced search, you'll find a help topic with that name. You'll also find dozens of similar solutions elsewhere in the forums.

[jonathandienst · Accepted solution]

Wrong syntax. try this:

sum({$<date = {"<=$(=Max(date))"}>} quantity)

[jianghong]

Thanks for the reply. Your syntax is correct and I tested the result.

However, the result is not as expected. I guess the date filter makes the scenario more complicated. Here are 4 different expressions in a table.

sum({$<[Date]={"<=$(=Max([Date]))"}>}[Quantity])

sum({1<[Date]={"<=$(=Max([Date]))"}>}[Quantity])

sum({$+<[Date]={"<=$(=Max([Date]))"}>}[Quantity])

sum([quantity])

1. When no date is selected, the last sum([quantity]} is the correct total quantity. Compared to that, only the third one has same result, total figure 26107. The total figure of the four columns: 25909, 25909, 26107,26107

2. Then when I select the latest date, the expected quantity should also be 26107, as I want total quantity up to the latest date. The total figures of the four columns are: 0, 25909, 198,198

3. When I select the earliest date, the expected quantity should be same as sum(quantity), as only a single date is calculated. The total figure of the four columns are: 0,0,1531,1531

In conclusion, none of the formulas have favourable result.. I have attached 3 screenshots for your reference.

[Rodj]

I think we might need to understand a little more about your data, specifically, how do the dates associate with the sku / quantity data? Is it a snapshot date for a set of data that also have record level dates or is it at record level? A screenshot of your data model would give us an ideal view. I've used the word associate deliberately, it can be dangerous to think of your date as a filter. Though filtering is basically what you are doing, you are filtering by an association so it is the association that becomes important and I suspect the issue here is more to do with understanding that in the context of your data model.

Your third expression is a bit nonsensical, it is essentially saying "add the set of max(date) to your existing selected set", but if the date exists in your selected set, the set logic doesn't add another set of data as it already exists. In other words it is going to give you the same result as simply summing the quantity with whatever other associations you filter by being applied.

Your first expression is simply saying, with my existing selections applied give me the result just for the set where date = max(date).

Your second expression is saying, out of the total set of data regardless of any selections give me the result just for the set where date = max(date).

In your first scenario your first two expressions are giving you the set where Date = Max(Date). Your third and fourth are giving you the sum of all records. This implies there are records in the app that aren't associated with the max(Date) value.

In your second scenario the first expression indicates that there are no records associated with the date, the second expression result doesn't change (which is the expected behaviour as it is ignoring selections), and your third and fourth expressions are telling us that is the result for the selected date, which by implication is different from max(Date). Are we therefore talking about two different dates here?

Your third scenario is implies we are talking about two different dates. We need to see your data model.

[jianghong]

The data model is just one excel sheet, no association.

I am testing the Qlik tool with a sample excel sheet containing date, sku, quantity. You can think the quantity like sales for each day and each sku. And the purpose is to sum the sales as of the selected date, so I need to sum all the sales records earliear than the selected date.

In some other BI tool I can simply define the date filter as '<= date', and that will do.

Is there any similar way to make that? I just feel it so complicated in Qlik to realize it, and you mentioned all the expressions give the result where date = max(date). I am confused, why the expression gives 'equal to' when it clearly contains {$<[Date]={"<=$(=Max([Date]))"}>}?

No, not 'equal to', from the start I want <= . How to make it happen?

Thank you very much for your help.

[Kashyap_R · Accepted solution]

Hi 

plz, go through the qvf file attached maybe you will get some idea.

Thanks

[jianghong]

Oh, your qvf file worked perfectly but I loaded the same column name and expression, then it returned other result... really strange... Finally I figured there was a bug in my expression.

Because I dragged auto calender date into the filter, the same expression did not work.

Sum({<orderDate={"<=$(=max(orderDate))"}>}Quantity)

Instead, the following expression worked.

Sum({<[OrderDate.autoCalendar.Date]={"<=$(=max([OrderDate.autoCalendar.Date]))"}>}Quantity)

Thank you all very much for the help and advices which all lead to the solution.

Let me attach the screenshot so you can see what happend. 

[Rodj]

Glad to hear you got it sorted out, I was struggling to work out why something that sounded incredibly simple was going wrong but totally forgot about the generated date values as I tend not to use them, something for me to remember!