topic Re: Cyclic in Bar Graph in QlikView

Cyclic in Bar Graph

bohravanraj — Thu, 12 Mar 2015 12:39:16 GMT

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

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 12:44:26 GMT

You can use a if statement for expressions. Something like this:

If(GetCurrentField(Cycle_Group) = 'Month', expression1, If(GetCurrentField(Cycle_Group) = 'Quarter', expression2...and so on

HTH

Best,

Re: Cyclic in Bar Graph

bohravanraj — Thu, 12 Mar 2015 12:49:05 GMT

Dear Sunindia,

Thanks for immediate reply, Can you please send a small application of it, it would really be very helpfull

Thanks

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 12:58:40 GMT

PFA the sample application.

Best,

Re: Cyclic in Bar Graph

bohravanraj — Thu, 12 Mar 2015 13:01:58 GMT

But here how it is possible to find the value for Maximum of month

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 13:02:58 GMT

Oh you want to get the max value of the month. Let me fix my expression and get back to you.

Best,

Re: Cyclic in Bar Graph

Anonymous — Thu, 12 Mar 2015 13:14:34 GMT

Hi find attached app .

if(GetCurrentField(Group)='Year',sum({<Month={'dec'}>}Value),if(GetCurrentField(Group)='Quarter',sum({<Month={'mar','jun','sep','dec'}>}Value),Value))

Re: Cyclic in Bar Graph

bohravanraj — Thu, 12 Mar 2015 13:15:02 GMT

Here User want the value of Last Month

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 13:15:49 GMT

Here you go. See if this is what you want.

Best,

Re: Cyclic in Bar Graph

nikhilgarg — Thu, 12 Mar 2015 13:25:42 GMT

Hey,

tried small example with year hardcoded 2014. In DImension i put Quarter , Month and in expression as:

Sum({<Year = {'2014'} , Month = {"=aggr(Max(Month), Quarter)"}>}Value)

It is giving em values for 1st month of Q1 and 1st month of Q2.

cAn anybody tell me why is it so when i have used Max(Month) ??

Thanks

Re: Cyclic in Bar Graph

nikhilgarg — Thu, 12 Mar 2015 13:27:24 GMT

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.

Re: Cyclic in Bar Graph

Anonymous — Thu, 12 Mar 2015 13:29:28 GMT

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

I dont think so

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 13:34:13 GMT

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,

Re: Cyclic in Bar Graph

Thu, 12 Mar 2015 13:44:10 GMT

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

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 13:51:00 GMT

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,

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 13:56:02 GMT

Updated it to have Year in the Cycle Group. PFA

Best,

Re: Cyclic in Bar Graph

nikhilgarg — Thu, 12 Mar 2015 14:16:29 GMT

I ahve only added Q1 and Q2

Re: Cyclic in Bar Graph

bohravanraj — Thu, 12 Mar 2015 14:34:49 GMT

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

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 14:59:53 GMT

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,

Re: Cyclic in Bar Graph

sunny_talwar — Thu, 12 Mar 2015 15:09:12 GMT

To answer you 1st question, please find attached.

Best,