Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the data as attached with fields as Store,MM,Month, Sales & Year. I have defined 2 variables as vMaxYear=max(Year) and vMaxMonth=max(Month_no).
I have seperately loaded an inline table linking the month corresponding to month_numbers.
Now, I use the following expression to show the monthly data in a straight table where I took store as a dimension.
Sum({$<Year={$(vMaxYear)},Month_no={$(vMaxMonth)}>}Sales)
The straight table doesn't fetch any result, after I selected a MM,Year, Month from my list boxes
Now, when I change this expression to =Sum({$<Year={'2012'},Month_no={'2'}>}Sales) in a text object, I can see the result.
Please help me understand, where am I going wrong.
I would also need to calculate YTD in this case, in the same straight table
Thanks In Advance,
Tutan
Create an input box and display your variables vMaxYear and vMaxMonth to verify that you are getting what you expect, regards!
Hi,
Try like this,
LOAD * INLINE [
MonthKey, Month, Quarter
1, Jan, Q1
2, Feb, Q1
3, Mar, Q1
4, Apr, Q2
5, May, Q2
6, Jun, Q2
7, Jul, Q3
8, Aug, Q3
9, Sep, Q3
10, Oct, Q4
11, Nov, Q4
12, Dec, Q4
];
And in original table, use like this,
Load *, Month(Date) * 1 as MonthKey from tablename;
Then use like this
Sum({$<Year={$(vMaxYear)},Month_no={$(vMaxMonth)}>}Sales)
Where vMaxMonth = Max(MonthKey)
Hope it helps