Skip to Content
author's profile photo Former Member
Former Member

adding custom columns on regular interval in crosstab report

Hi ,

I have simple report of State wise Month wise Sales .

I am showing 12months in columns and states in rows .

My requirement is after every 3 months I want to show a column which will give me sum of those 3 months as a Quarter.

I am tying this XI 3.1.


any idea on this ??


Thank you in advance

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2014 at 01:14 PM

    Hi,

    Create the cross Tab report as below and apply the break in Q, in that Q column just provide the formula as Sum(Sales) In (Q).

    if you have Q object it fine, otherwise create a variable for quarter as below

    Var_Qtr= if([Month] Inlsit(1;2;3);"Qtr1";if([Month] Inlist (4;5;6);"Qtr2";if([Month] Inlist (7;8;9);"Qtr3";if([Month] Inlist (10;11;12);"Qtr4"))))

    Create the cross tab with two rows (Month & Qtr) one column (State) and drag the sales measure in data values place. now apply the break in Qtr row. then provide the formula in that column as Sum([Sales] in ([Qtr]))

    Thanks,

    Sakthi.


    Sales.jpg (48.7 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 04, 2014 at 11:51 AM

    You need an extra dimension for Quarter on which you need to apply break. You can create a variable i webi to obtain the quarter from month. But as in XI3.1, there is no option to hide dimension, you need to reduce the width of the quarter column to 4px and make the font and background color same so that it appears to be hidden.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 04, 2014 at 11:31 AM

    Hi,

    restrict measue with that month

    for example ur having month in var1

    = If(var1>=1 And var1<=3) Then "Q4" Else If (var1>=4 Andvar1<=6) Then "Q1"

    Else If(var1 >=7 And <=9) Then "Q2" Else If (v ar1>=10 And var1<=12) Then "Q3"

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 04, 2014 at 12:54 PM

    Hi Tushar,

    In this case, it may be better you go with vertical table rather than cross tab..

    if it is cross tab, then following issues may come

    difficult to achieve your requirement

    difficult to achieve sort on month, if you need..

    In vertical table, you need to create sixteen variables, one variable for each column..[12 months + 4 quarters].. need check the performance of report..

    each variable for month should contain formula like sum[sales] where month=<Month Name/No>

    each variable for quarter should contain formula like sum[sales] where month<=<month no> and month >=<month no>

    Hope this helps..

    Thanks,

    Bala

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2014 at 09:22 AM

    Thank you guys for your suuggestion 😊

    Ganapathy - by your views we can show Quarters but i want to show months as well ,thansk

    Arjit - we r almost done it but formatting and hiding the columns is bit difficult , thanks man

    Bala - cross tab is the requrement and create 1 variables will hamper the performance , thanks for views

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Ah, so you want to show the quarter footer but not the quarter above the Month name.

      Reather than removing it completely, you could show it by removing duplicates and centering across the break - it looks better like that.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.