topic Re: Dynamic get the newest data by FirstSortedValue statement in QlikView

Dynamic get the newest data by FirstSortedValue statement

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Dynamic get the newest data by FirstSortedValue statement

Thu, 17 Aug 2017 03:00:56 GMT

Nobody knows?

Re: Dynamic get the newest data by FirstSortedValue statement

tomovangel — Thu, 17 Aug 2017 07:11:48 GMT

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

Re: Dynamic get the newest data by FirstSortedValue statement

Anonymous — Thu, 17 Aug 2017 07:58:04 GMT

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)

Re: Dynamic get the newest data by FirstSortedValue statement

Thu, 17 Aug 2017 08:21:06 GMT

Hi Robin

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

Re: Dynamic get the newest data by FirstSortedValue statement

Anonymous — Thu, 17 Aug 2017 08:32:56 GMT

I was wrong.

Re: Dynamic get the newest data by FirstSortedValue statement

techvarun — Fri, 18 Aug 2017 05:33:21 GMT

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 .

Re: Dynamic get the newest data by FirstSortedValue statement

Fri, 18 Aug 2017 05:49:58 GMT

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.

Re: Dynamic get the newest data by FirstSortedValue statement

Fri, 18 Aug 2017 07:30:18 GMT

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