topic Re: Need help with Straight table in QlikView

Need help with Straight table

Thu, 19 Jul 2012 18:10:35 GMT

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

Re: Need help with Straight table

edg_baltazar — Thu, 19 Jul 2012 19:07:32 GMT

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

Re: Need help with Straight table

MayilVahanan — Thu, 19 Jul 2012 20:08:02 GMT

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