Dynamic get the newest data by FirstSortedValue statement

[#1]

Hi All

Based on my previous two questions:

Get the latest value for each department

Firstsortedvalue from multi columns

I can achieve my purpose: get the newest data from data sheet by firstsortedvalue function.

However, there's one problem, if one of condition has multi options, I have to split one by one.

For example:

In the attachment,

Sum({<Type={'売 上 高　実　績'},Key={"$(=FirstSortedValue({<FSVKey={'売 上 高　実　績预算'},TestedDate={'2016-10'}>} Key, -Key))"}>} Data)

Sum({<Type={'売 上 高　実　績'},Key={"$(=FirstSortedValue({<FSVKey={'売 上 高　実　績预算'},TestedDate={'2016-11'}>} Key, -Key))"}>} Data)

Sum({<Type={'売 上 高　実　績'},Key={"$(=FirstSortedValue({<FSVKey={'売 上 高　実　績预算'},TestedDate={'2016-12'}>} Key, -Key))"}>} Data)

I can exact the newest data per month, but I can't write like this:

Sum({<Type={'売 上 高　実　績'},Key={"$(=FirstSortedValue({<FSVKey={'売 上 高　実　績预算'},TestedDate={'2016-10','2016-11','2016-12'}>} Key, -Key))"}>} Data)

That means, as each year has 12 months, I need to write 12 statements in expression.

It will be a heavy working load for maintenance.

And the FSVKey also has multi options, like '売 上 高　実　績实际', '売 上 高　実　績预算', '税 引 前 利 益(Profit)预算' and so on.

The split will keep on and on.

So I wonder is there any better solution like using variable to cover multi option issue. Combine those statements into one.

I checked and searched some documents, found nothing.

Any suggestions will be appreciated.

[#2]

Nobody knows?

[tomovangel]

I guess, the Non-english symbols and words, are giving the community confusion, perhaps you can translate the whole post in English?

[Anonymous]

not sure if I understood your question, perhaps you could use the ColumNo() function for saving time creating the expressions, but it won't calculate faster...

Sum({<Type={'売 上 高　実　績'},Key={"$(=FirstSortedValue({<FSVKey={'売 上 高　実　績预算'},TestedDate={'2016-$(=ColumnNo())'}>} Key, -Key))"}>} Data)

[#5]

Hi Robin

I tried your solution, but it doesn't work, the chart display nothing.

[Anonymous]

I was wrong.

[techvarun]

Hi,

I tried multiple ways but no luck. stacked bar chart seems to be a solution but in case we have two dimensions, we can use only one expression .

[#8]

Hi Varun

I tried to use both firstsortedvalue and max these two function, however, the number in chart was not right.

When I added three dimensions, the chart will automatically change to stacked style.

I just wonder whether it is feasible like this:

First I filter the data from FSVKey (this column is combined by F2 & Type & TestedDate), then get the maximum key.

Finally, use the dimension to show data by group.

But I failed to find the way out.

[#9 · Accepted solution]

I got the solution, use firstsortedvalue and max function in expression, then setup if function in dimension, the chart is working now.