topic Re: Get all previous dates in QlikView

Get all previous dates

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Get all previous dates

swuehl — Fri, 15 Apr 2016 22:17:31 GMT

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

Re: Get all previous dates

Fri, 15 Apr 2016 22:29:30 GMT

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.

Re: Get all previous dates

johnw — Fri, 15 Apr 2016 22:47:52 GMT

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.

Re: Get all previous dates

oscar_ortiz — Fri, 15 Apr 2016 22:48:55 GMT

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:

Re: Get all previous dates

swuehl — Fri, 15 Apr 2016 23:17:08 GMT

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

Re: Get all previous dates

Fri, 15 Apr 2016 23:18:55 GMT

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.