Bar Chart Issue

[er_mohit]

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

[johnca]

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?

[JonnyPoole]

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

[sundarakumar]

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

[er_mohit]

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.

[Anonymous]

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;

next

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);

next

Hope this helps.

Best Regards

Stefan

[er_mohit]

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

[Anonymous]

Hi,

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

Best Regards

Stefan

[er_mohit]

See the attached file

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

Regards

Mohit

[#10]

hi

see attachment