Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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.
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.
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:
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
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.