8 Replies Latest reply: Mar 22, 2014 2:32 AM by Dan Posthumus RSS

    Expression as a dimension

      Hi

       

      I would like to have a bar chart with document type (Invoices, Orders, Quotations) as a dimension and the Rand values as an expression. The client also want to see the sum(budget) as a separate dimension as per image below.

       

      How does one show an expression as a separate dimension?

      chart.png

        • Re: Expression as a dimension
          Alessandro Saccone

          try to add a new dimension to your data set that is add a field "document type" associated to your document (when you load or select) and use it as dimension

          • Re: Expression as a dimension
            sunil kumar

            In you script

            make

            Load  sum(budget)  as Sum_Budget from data source;

            then add  (Sum_Budget)  into your Dimension

            Hope your problem get solved

             

            or

            On Dimension tab see add calculated Dimension as sum(budget)

            if you want label it

              • Re: Expression as a dimension

                Hi Sunil

                My script looks like this

                SALES:
                Load
                Date(DocDate) as DocDate,
                FinncPriod,
                ObjType,
                slpcode,
                slpname,
                Turnover;


                SQL SELECT Branch,
                    DocDate,
                    FinncPriod,
                    ObjType,
                    slpcode,
                    slpname,
                    Turnover
                  FROM dbo."XBI_SalelinesNew";

                 

                Concatenate
                Load
                Budget as Target,
                Fincpriod as FinncPriod,
                Name as slpname,
                SlpCode as slpcode
                ;

                SQL SELECT Budget,
                    Fincpriod,
                    Month,
                    Name,
                    SlpCode,
                    "U_Year"
                FROM dbo."XBI_SalesBudgetIncludingEmpCode";

                 

                All goes well up tp here.

                 

                I then want to do your suggestion 'Load  sum(budget)  as Sum_Budget from data source' to make a new dimension so -

                 

                BudgetTotals:
                Load
                slpcode,
                Sum(Target) as TargetDimension
                Resident SALES;

                 

                Then I get this error

                Invalid expression
                BudgetTotals:
                Load
                slpcode
                Sum(Target) as TargetDimension
                Resident SALES;

                 

                I can't see where I'm making the error?

                 

                 


                  • Re: Expression as a dimension
                    sunil kumar

                    I noticed that

                    you are  making  "Budget as Target" field  leave as it is Budget then use like  below

                    BudgetTotals:

                    Load

                    slpcode

                    Sum(Budget ) as TargetDimension

                    Resident SALES;

                    or

                    you can directly use  Sum(Budget ) in your calculated dimension these not make any difference 

                • Re: Expression as a dimension
                  Massimo Grossi

                  SCRIPT

                  Table: load 1500 as budget AutoGenerate 1;

                   

                  CHART

                  dimension         

                  ValueList('Invoices', 'Orders', 'Quotations', 'Budget')

                   

                  expression         

                  if(match(ValueList('Invoices', 'Orders', 'Quotations', 'Budget'), 'Invoices', 'Orders', 'Quotations'),

                  rand()*1000,

                  sum(budget)

                  )

                   

                   

                  2014-03-21 23_37_14-QlikView x64 - [C__Users_mgrossi_Desktop_Z.qvw_].png

                    • Re: Expression as a dimension

                      Hi Massimo

                      Thx for the reply - I'm getting close now but my totals in the chart is wrong - probably something small. See image below - I've added the correct values in text boxes below. When I implement your suggestion - Am I not getting budget (target) amounts per doc type and the total budget (target) as a separate column? What I need is the turnover

                      sum(Turnover) per doc type and the Sum(Target) as separate column like in your chart. The chart looks exactly like I want it - it's just the totals that are incorrect.

                      chart.png