topic Bar Chart Issue in QlikView

Bar Chart Issue

er_mohit — Fri, 26 Sep 2014 12:14:25 GMT

Hi Friends

I'm stuck of facing the problem, Please see below attached image and i want to build that bar graph dynamically.

I also try to build a logic on back end in Master Calendar but doesn't get succeed.

Any help will be appreciated

Thanks & Regards

Mohit

Re: Bar Chart Issue

johnca — Fri, 26 Sep 2014 12:34:19 GMT

Interesting chart. Appears to show the first 2 quarters, the first half, the last two months, and the ytd values, and you'd like all values in each bucket as appropriate. I don't think QlikView can put a single value into more than one bucket ( ie, count it twice). Anyone?

Re: Bar Chart Issue

JonnyPoole — Fri, 26 Sep 2014 12:41:17 GMT

There are 2 approaches and both involve setting the data model up right

1. Have Base/Actual as a boolean field in the data model and use it as a dimension. Then add 5 expressions in the chart for each grouping of Base/Actual. This will probably require you to pivot the table in the load script.

2. Create a custom lookup table that maps the date buckets to the detailed months.

So if your main table had Month, create a lookup table that maps each bucket to all the months that go into it like this. ChartValue would then become your dimension and Base and ACtual would be your 2 expressions. The table could be calculated dynamically based off logic to create the appropriate buckets depending on which month you reload.

Month ChartValue

Jan-2014 Q1

Feb-2014 Q1

Mar-2014 Q1

Apr-2014 Q2

May-2014 Q2

Jun-2014 Q2

Jan-2014 H1

Feb-2014 H1

Mar-2014 H1

Apr-2014 H1

May-2014 H1

Jun-2014 H1

Jul-2014 Jul&Aug

Aug-2014 Jul&Aug

Jan-2014 YTD Aug

Feb-2014 YTD Aug

Mar-2014 YTD Aug

Apr-2014 YTD Aug

May-2014 YTD Aug

Jun-2014 YTD Aug

Jan-2014 YTD Aug

Feb-2014 YTD Aug

Mar-2014 YTD Aug

Apr-2014 YTD Aug

May-2014 YTD Aug

Jun-2014 YTD Aug

Jul-2014 YTD Aug

Aug-2014 YTD Aug

Re: Bar Chart Issue

sundarakumar — Sat, 27 Sep 2014 09:02:00 GMT

value list may help in this case.Please check the below link

http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/01/valuelist-for-those-tricky-situations

-Sundar

Re: Bar Chart Issue

er_mohit — Sat, 27 Sep 2014 09:37:20 GMT

Thanks for your reply.

Actually i want it as dynamic purpose . i want this type of chart as per user selection made of year. Suppose if user select Year = 2013 then this graph 'll show

Q1, Q2, Q3, Q4, H1,H2,YTD Dec because all the data have been covered up for all month for Actual and base.

similarly if user select 2014 then Graph 'll show

Q1,Q2,Q3,H1,YTD Sep

and in above show graph the data upto Aug so, there is a accumulate data for July & Aug

Hope it helps you.

Re: Bar Chart Issue

Anonymous — Sat, 27 Sep 2014 10:51:28 GMT

Hi,

if want to do this dynamically then dynamically generate the list Jonathan presented in script like this:

MasterTableDimension:

Load

Null() as ChartValue

Autogenerate (0);

left join (MasterCalendar)

Load

Year,

max(Month) as MaxMonth

Resident MasterCalendar group by Year;

for each vDim in 'Quarter', 'HalfYear'

Concatenate(MasterTableDimension)

load

Date,

$(vDim) as ChartValue

Resident MasterCalendar;

Years:

load distinct

Year

Resident MasterCalendar;

for I = 0 to (noofrows('Years')-1)

let vYear = peek('Year', $(I), 'Years');

Concatenate(MasterTableDimension)

load

Date,

addmonths(MaxMonth, -1) & chr(38) & MaxMonth as ChartValue

Resident MasterCalendar where Year = $(vYear) and Month >= addmonths(MaxMonth, -1) ;

Concatenate(MasterTableDimension)

load

Date,

'YTD ' & MaxMonth as ChartValue

Resident MasterCalendar where Year = $(vYear);

Hope this helps.

Best Regards

Stefan

Re: Bar Chart Issue

er_mohit — Tue, 30 Sep 2014 09:42:22 GMT

Hi S_Kunte23

Thanks for your feedback and its really helpful. and while implementing this script after loading MasterCalendar,

when i write this

left join (MasterCalendar)

Load

Year,

max(Month) as MaxMonth

Resident MasterCalendar group by Year;

and reload it then message occur

Execution of script failed. Reload old data.

m stuck off facing the problem please help me.

Thanks & Regards

Mohit

Re: Bar Chart Issue

Anonymous — Tue, 30 Sep 2014 10:10:11 GMT

Hi,

please post sample qvw. Or a screenshot of your data model from table viewer.

Best Regards

Stefan

Re: Bar Chart Issue

er_mohit — Tue, 30 Sep 2014 10:27:40 GMT

See the attached file

here i upload the script of master calendar with your code provided above.

Regards

Mohit

Re: Bar Chart Issue

Tue, 30 Sep 2014 12:11:00 GMT

see attachment

Re: Bar Chart Issue

er_mohit — Tue, 30 Sep 2014 12:16:55 GMT

Hi VISHWARANJAN

Thanks for reply.

When i reload it execution fail

Re: Bar Chart Issue

perumal_41 — Tue, 30 Sep 2014 12:31:04 GMT

PFA,

hope it helps

Re: Re: Bar Chart Issue

Anonymous — Tue, 30 Sep 2014 17:23:05 GMT

Hi,

due to your date formats I had to modify the script, see attached qvw!

Hope this helps.

Best regards

Stefan

Re: Re: Bar Chart Issue

er_mohit — Wed, 01 Oct 2014 04:15:43 GMT

Superb Stefan Kunte

Good job as expected.

Thanks & Regards

Mohit

Re: Re: Bar Chart Issue

er_mohit — Wed, 01 Oct 2014 04:39:53 GMT

Thanks for reply as mentioned in screenshot above i want same alike in graph.

i see your attachment it solve the purpose for Quarterly wise not month wise . like

Suppose if user select Year = 2013 then this graph 'll show

Q1, Q2, Q3, Q4, H1,H2,YTD Dec because all the data have been covered up for all month for Actual and base.

similarly if user select 2014 then Graph 'll show

Q1,Q2,Q3,H1,YTD Sep

and in above show graph the data upto Aug so for that

Q1,Q2,July & Aug,H1,YTD Aug

hope it helps

Re: Re: Bar Chart Issue

Wed, 01 Oct 2014 05:42:56 GMT

Superb Stefan Kunte

Thanks & Regards

Vishwaranjan Kumar

Re: Re: Bar Chart Issue

er_mohit — Fri, 10 Oct 2014 08:18:15 GMT

Hi Stefan Kunte

I have a problem in this regards and want a new solution for that during implement of this it repeats the value thrice a time and also for two combined month is not true it always pick up MaxMonth and MaxMonth-1 .

I need if Month complete the Quarter then Quarter show otherwise not show. like

Year 2014 have data upto Aug so, my dynamic dimension is

Q1,Q2,H1,July & Aug,YTD Aug

as shown above attached graph.

Is there any other solution on the based of both field Month and MaxMonth field to put an if condition

Regards

Mohit

Re: Bar Chart Issue

Anonymous — Fri, 10 Oct 2014 08:58:52 GMT

Hi,

then you have to modify the date range of your calendar from the example you posted you have:

LET vDateMin = Num(MakeDate(2006,1,1));

LET vDateMax = Num(Today());

define these start and enddates according to their occurence in your facttable for example:

Temp:

load

min(Date) as Min,

max(Date) as Max

Resident Facttable;

LET vDateMin = Num(peek('Min', 0, 'Temp'));

LET vDateMax = Num(peek('Max', 0, 'Temp'));

drop table Temp;

Best regards

Stefan

Re: Re: Bar Chart Issue

er_mohit — Fri, 10 Oct 2014 11:32:02 GMT

Ok. thanks for your reply. As per your code i just want to one table means merging of two tables to Final table but i got thrice time count of Quarters and Half Year . Please see attached.

I want this both tables into One with no repetition

Any help will be appreciated.

Thanks & regards

Mohit

Re: Bar Chart Issue

Anonymous — Fri, 10 Oct 2014 13:02:38 GMT

Hi,

yes your Calendar will "grow" if you join the ChartDimension Field into the Calendar because for example Half Year and Quarter have common dates.

Best regards

Stefan