topic Re: Variable in set analysis in QlikView

Variable in set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

I have a table that contains Ending balance for each period for each product

Then I created variable to display the most recent period for each project: vLastEndingBalance = Aggr(max(Period),[Project ID]).

Then I would like to create a pivot table that will show me the project ID and Ending Balance based on period selection, but when no period selected it should present the balance for most recent period.

So I created a pivot table with dimension "Project ID" and as expression I have:

=only({<Period ={"=$(=vLastEndingBalance)"}>} Ending_Balance).

It works fine when I select Period but when there is no selection the results of the expression gives me the earliest balance instead of recent

can you kindly let me know where I have a problem.

Thanks

Re: Variable in set analysis

krishnacbe — Fri, 28 Oct 2016 18:56:00 GMT

Try using below expression

=only({<Period ={"=$(vLastEndingBalance)"}>} Ending_Balance).

Re: Variable in set analysis

sunny_talwar — Fri, 28 Oct 2016 18:59:55 GMT

Not sure, but may be try this:

=Only({<[Project ID] = {"=Period = Max(TOTAL Period)"}>} Ending_Balance)

Re: Variable in set analysis

sunny_talwar — Fri, 28 Oct 2016 19:00:55 GMT

Or this:

FirstSortedValue(Ending_Balance, -Period)

Re: Variable in set analysis

Fri, 28 Oct 2016 19:54:13 GMT

That one worked!

Thank you Sunny.