Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm new to QV and I'm currently trying to manage a cross table with a list of "dynamic dimensions" (because I would like to manage data from several facts' tables). This cross table is a kind of global summary.
Thus I've created a new chart, defined as a cross table, and set the following calculated dimension:
ValueLoop(vTodayYear, vTodayYear - vNbOfYear + 1, -1)
where :
vTodayYear = Year(Today()) // Contains the current year
vNbOfYear = 4 // Contains the number of years that I want to display in my chart. This value can be modified by the EU
The dimension is correctly created but now I would like to define my expressions. I've tried many different expressions without any success. For example, if I want the orders' amount by year, I tried something like that:
Sum({$<OrderYear={$(=vTodayYear - vModellingYears + ColumnNo())}>} [OrderAmount])
I've also seen some implementations using pick/match functions but was not able to make it work in my case.
Basically the question is: how can I use/retrieve the current value of my dimension (that is to say the year) in my chart's expressions ?
Maybe I'm doing this wrong or the tool is not designed for such things. Any help would be appreciated.
Thanks!
You can't use ColumnNo() in set analysis. The set is calculated at the chart level where ColumnNo has no meaning. If you want to use the dimension value in the set analysis expression you will have to use a pick-match combination. See this document: Evaluating "sets" in the context of a dimension
ok, thanks for your quick answer !