topic Re: How create specific chart in QlikView

How create specific chart

Fri, 06 Mar 2015 08:10:44 GMT

Hello,

I'm beginner of QlikView.

I have a problem to create chart.

I want display lastYear column and lastHalfYear column and one year eariler second column. All in one chart.

My aim is in attachment.

I try add calculated dimension.

= if (right(halfYear, 4)='2013', dual(Year(Today())-2,Year),

if (right(halfYear, 2)='6/', dual('YTD'&halfYear,halfYear) ))

Should I in different way read data?

Thanks for help!

Daniel

Re: How create specific chart

MK_QSL — Fri, 06 Mar 2015 08:12:40 GMT

Provide sample data.. will try to work on this..

Re: How create specific chart

Fri, 06 Mar 2015 13:17:31 GMT

Sample data.

Dimension time

For every type(in example data only one) caluclate SUM(amount*price).

Re: How create specific chart

JonnyPoole — Fri, 06 Mar 2015 15:10:10 GMT

Here is a sample

let me know if you are unable to open the QVW

Re: How create specific chart

Fri, 06 Mar 2015 16:45:28 GMT

I can't open this document. I don't have named user cal license.

Re: How create specific chart

JonnyPoole — Fri, 06 Mar 2015 16:53:12 GMT

Ok. so my script is:

LOAD year,

month,

yearMonth,

halfYear,

quarter,

// [code ] as Code,

productName,

amount,

price

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

i have a variable vLastYear with an expression: =max({1} year)-1

And my chart has no dimensions and 3 expressions .

Expression 1; sum( {$<year={$(vLastYear)}>} amount * price)

Expression 2: sum( {$<year={$(vLastYear)},month={"<=6"}>} amount * price)

Expresion 3: sum( {$<year={$(=vLastYear-1)},month={"<=6"}>} amount * price)

you can also put an expression in the label too:

Expression 1 label: $(vLastYear)

Expression 2 label: $(vLastYear) & '6'

Expression 3 label: $(=vLastYear-1) & '6'

Re: How create specific chart

Fri, 06 Mar 2015 22:32:08 GMT

Thank you for answer.

I have another one question.

When product have 3 different code and I want create stacked chart.

For example:

Clik 2013

Chart have main bar with sum for 2013 for stacked 3 different codes.

Different bars have stacked value for YTD2013, YTD2014, YTD2015

Re: How create specific chart

Mon, 09 Mar 2015 08:15:40 GMT

Aim is in *.png file in first post.

Anyone can help?

Re: How create specific chart

JonnyPoole — Mon, 09 Mar 2015 13:58:06 GMT

You can use the same script, but you need to add a synthetic dimension and instead of 3 expressions, use 1 conditional expression:

1. Add a new 'calculated dimension' to the chart and promote it to be the first dimension. The formula for the calcualted dimension will be a synthetic dimension as follows:

=ValueList('CurYear','LastYearYTD','2YearsYTD')

Also add a product dimension and ensure its 2nd (from the top) in the list.

2. Delete all the expressions and add a new expression with the following conditional formula

if(ValueList('CurYear','LastYearYTD','2YearsYTD')='CurYear',

sum( {$<year={$(vLastYear)}>} amount * price) ,

if( ValueList('CurYear','LastYearYTD','2YearsYTD') = 'LastYearYTD',

sum( {$<year={$(vLastYear)},month={"<=6"}>} amount * price) ,

sum( {$<year={$(=vLastYear-1)},month={"<=6"}>} amount * price)

)

You can also put the following dynamic label formula:

=if( ValueList('CurYear','LastYearYTD','2YearsYTD')='CurYear',

$(vLastYear) ,

if( ValueList('CurYear','LastYearYTD','2YearsYTD') = 'LastYearYTD',

$(=vLastYear-1) & ' - 6' ,

$(=vLastYear-2) & ' - 6'

)

3. on the style tab, change from 'grouped' to 'stacked'