8 Replies Latest reply: Mar 9, 2015 9:58 AM by Jonathan Poole RSS

    How create specific chart

      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
          Manish Kachhia

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

            • Re: How create specific chart

              Sample data.

               

              Dimension time

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

                • Re: How create specific chart
                  Jonathan Poole

                  Here is a sample

                   

                  let me know if you are unable to open the QVW

                    • Re: How create specific chart

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

                        • Re: How create specific chart
                          Jonathan Poole

                          Ok. so my script is:

                           

                          LOAD year,

                               month,

                               yearMonth,

                               halfYear,

                               quarter,

                          //    [code ] as Code,

                               productName,

                               amount,

                               price

                          FROM

                          [C:\Temp\example_data.csv]

                          (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'



                          Capture.PNG

                            • Re: How create specific chart

                              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
                                  Jonathan Poole

                                  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'

                                   

                                   

                                  Capture.PNG

                      • Re: How create specific chart

                        Aim is in *.png file in first post.

                        Anyone can help?