Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.