topic Use the selected value as variable $::column in QlikView

Use the selected value as variable $::column

atsushi_saijo — Tue, 10 Feb 2015 16:57:10 GMT

I have to create a set analyses which uses user's selected month as variable, and sum any document not more than that month.

(*If user selected Nov-2014, the set analysis must sum all the past up to Nov-2014.)

These months are quite combined master calendar, and another expression uses only the month's sum. (This is used in the accounting GL statistics.)

Let's say, user selected Dec2014. I must do sum().

So I have created Dual(MonthName(datevalue),Num(MonthEnd(datevalue))) as Month.

I was thinking this would work:

Sum({<Month={"<=$::Month"}>} Value)

But resulted value is 0.00.

Would you advise how I can make this work?

***********************

*Sample App is attached.

======Loading Script=========

Tmp: Load * Inline [

datevalue, txvalue

15/11/2014,5

15/12/2014,4

15/01/2015,6

15/02/2015,5

];

NoConcatenate

Test: Load Dual(MonthName(datevalue),Num(MonthEnd(datevalue))) as Month,txvalue as Value Resident Tmp; Drop Table Tmp;

======Set Analysis======

Sum({<Month={"<=$::Month"}>} Value)

I appreciate for your feedback in advance.

Re: Use the selected value as variable $::column

atsushi_saijo — Wed, 11 Feb 2015 09:44:09 GMT

I have referenced and noted comments on:

http://community.qlik.com/message/711276#711276

In this example, the selector is passed as text, even if loading script has passed the value as Dual. Consequently, it seems only option we could apply is to use Document Triggers. By setting 2 actions for Month selections as below:

By this, we forcefully select Mth field (=numeric field ), which can be used in the set analysis:

=Sum({<Mth={"<=$(=$(vSelectMth))"},Month=>} Value)

*$(vSelectMth) is defined in the loading script as:

Set vSelectMth = GetFieldSelections(Mth);

Test App is attached.

*I will leave this post open for a while, so that smarter solution could be posted by fellow BI practitioners.

Re: Use the selected value as variable $::column

atsushi_saijo — Wed, 11 Feb 2015 10:24:10 GMT

QuickNote: It might be that I do not need any of these, but simply $(=Max(Month)) might have worked.

**Yes, the dual value, such as MonthName() does not require any. This topic is resolved.