Need help with Straight table

[#1]

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

[edg_baltazar]

Create an input box and display your variables vMaxYear and vMaxMonth to verify that you are getting what you expect, regards!

[MayilVahanan]

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