Get all previous dates

[#1]

I have a variable investmentFinishDate that is a date dynamically setted scrolling a slider.

Something like this:

How can I retrieve all previous dates to investmentFinishDate?

P.S. I'm not able to open qlikview file because I have a personal edition. Thank you.

[swuehl]

All previous dates given what data set?

Are you maybe  looking for a set expression like

=Count({<DateField = {"<=$(investmenFinishDate)"}>} DISTINCT DateField)

Take care that your variable value is matching the field format of DateField:

Dates in Set Analysis

A Primer on Set Analysis

[#3]

Ok, I have an investmentFinishDate variable and I want to build a pivot table where for each date precedent to investmentFinishDate I have to use this expression
=(sum(aggr(FirstSortedValue([Adj Close],-Date)*Stocks_Qty-Total_Inv,Equity)))*100/Budget.

However, thank you for your availability.

[johnw]

You could consider making investmentFinishDate a field instead of a variable, then creating a table that links each investmentFinishDate to all previous dates. We generally call this an "AsOf table". Someone may have a better link, but if you scroll down through this document, you'll see the idea discussed.

Calculating rolling n-period totals, averages or other aggregations

There are a lot of ways to build the AsOf table. We can help you if you decide to go this way but are having trouble with it.

[oscar_ortiz]

You can try adding a button with the Select in Field Action.  using your variable and the date field in your data set.  So something like this:

[swuehl]

Ok, this means that you already have a solution now?

Then please close this thread (you can set it to Assumed Answered just to close it).

If you still have open issues, please clarify what your setting is. Best by uploading a small data set / qvw and your expected results.

edit:

Just guessing that you may want to use a set expression here:

=(

   sum(

     {<Date = {"<$(investmentFinishDate)"} >}

     aggr(

          FirstSortedValue({<Date = {"<$(investmentFinishDate)"} >} [Adj Close],-Date)*Stocks_Qty-Total_Inv

          , Equity  )

     )

)*100/Budget

In addition to the already mentioned blog posts, also have a look at

Set Analysis in the Aggr function‌

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

[#7]

Thanks for your answer.

I want to implement a table where for each date precedent to a variable investmentFinishDate I have to associate this expression:

=(sum(aggr(FirstSortedValue([Adj Close],-Date)*Stocks_Qty-Total_Inv,Equity)))*100/Budget.

So if, for example, my investmentFinishDate is set to 22/09/2012 I have to keep all dates precedent to 22/09/2012 and for each date I have to calculate the expression above.
Clearly I imported a table Date where there are all dates I need.