Cyclic in Bar Graph

[bohravanraj]

Dear Folk,

I am Facing a issue in creating a Bar Chart with Cyclic Expression, where there is no problem with showing month wise data, but when user want to see Quarter wise data in Bar graph then graph should show the data of Maximum of month(for example for Q1 it should show March Data,for Q2 it should Show Jun Data, And so on) similaly for Year wise it should show Dec Data in Cyclic Dimension.

Pleas see The following Excel for more Under Standing.

Year	Quarter	Month	Value
2014	Q1	jan	100
		feb	124
		mar	148
	Q2	apr	172
		may	196
		jun	220
	Q3	july	244
		aug	268
		sep	292
	Q4	oct	316
		nov	340
		dec	364

  Please See anybody can Help.

Thanx

[nikhilgarg]

Hey ,

I am using PErsonal edition , can you tell me what expression you used ??

I used Sum({<Year = {'2014'} , Month = {"=aggr(Max(Month), Quarter)"}>}Value) , but it is giving me 1st month values for all quarters.

[Anonymous]

Nikhil .. is your expression also giving first month for Q3 and Q4 ?

I dont think so

[sunny_talwar]

Script:

Temp:

LOAD * INLINE [

    Date, Value

    01/01/2014, 192

    02/01/2014, 124

    03/01/2014, 148

    04/01/2014, 240

    05/01/2014, 196

    06/01/2014, 220

    07/01/2014, 244

    08/01/2014, 268

    09/01/2014, 292

    10/01/2014, 316

    11/01/2014, 340

    12/01/2014, 364

];

Join (Temp)

LOAD *,

  1 as Flag

Resident Temp

Where Mod(Month(Date), 3) = 0;

Table:

LOAD *,

  Year(Date) as Year,

  'Q' & Ceil(Month(Date)/3) & '-' & Year(Date) as QuarterYear,

  MonthName(Date) as MonthYear

Resident Temp;

DROP Table Temp;

Expression:

=If(GetCurrentField(Cycle_Group) = 'MonthYear', Sum(Value), Only({<Flag = {1}>}Value))

Output:

HTH

Best,

S

[#5]

Hi,

Try this expression in expression tab and in dimension tab create the cyclic group fields.

Max ({<MonthYear={'Mar 2014','Jun 2014','sep 2014','Dec 2014'}>}Value)

Thanks,

krish

[sunny_talwar]

I feel that this is a solution, but its not scalable. If he wants to see 10 years worth of quarter, he would have to enter 40 MMM YYYY within the set analysis. I think putting the flag would be a much more efficient way of doing it.

Best,

S

[sunny_talwar]

Updated it to have Year in the Cycle Group. PFA

Best,

S

[nikhilgarg]

I ahve only added Q1 and Q2

[bohravanraj]

dear sunindia,

here i have two doubts:

• how can it be done for current quarter and year, since it will not be showing the value of current quater and year until last month of that quarter doesnt comes.
• How can it be done on Fiscal Year

Thanx

[sunny_talwar · Accepted solution]

Fiscal Year is easy. Currently I am dividing by 3 because I am assuming the year end to be Dec.

Join (Temp)

LOAD *,

  1 as Flag

Resident Temp

Where Mod(Month(Date), 3) = 0;

DROP Table Temp;

If your years end is Jan (for example)

Join (Temp)

LOAD *,

  1 as Flag

Resident Temp

Where Mod(Month(AddMonths(Date, -1)), 3) = 0;

DROP Table Temp;

This will now flag the 1st Month,  4th Month, 7th Month and 10th Month for quarters.

Still need to work on your 1st question

Best,

S

[sunny_talwar]

To answer you 1st question, please find attached.

Best,

S