9 Replies Latest reply: Apr 11, 2016 12:41 PM by Sunny Talwar RSS

    Class function

    Prasanta Kumar

      Hi,

       

      I want to create a dimension using class function like below for a bar chart.

       

      My dimension is Customer Name

       

      and measurement is Gross Profit = sum(revenue)-(sum(cost)+sum(additional services))

       

      My interval will be 10000.

       

      can you please help me with the syntax.

       

      how i can put the calculation for Gross profit inside the expression, to see how many customers fall under 0 to 10000 or 10000 to 20000 gross profit bucket.

       

       

      thanks

      Prasanta

        • Re: Class function
          Sunny Talwar

          I guess something like this:

           

          Dimension:

          Class(Aggr((Sum(revenue)-(Sum(cost)+Sum(additional services))), [Customer Name]), 10000)

           

          Expression:

          Count(DISTINCT [Customer Name])

            • Re: Class function
              Prasanta Kumar

              this is not working in my case

                • Re: Class function
                  Sunny Talwar

                  Would you be able to share some sample data?

                  • Re: Class function
                    Sunny Talwar

                    Seems to be working for me in the attached sample. Can you check to see if you have the same structure?

                     

                    Capture.PNG

                     

                    Sample script used:

                     

                    Table:

                    LOAD *,

                      Ceil(Rand() * (50000 - 25000) + 25000) as Revenue,

                      Ceil(Rand() * (15000 - 10000) + 10000) as Cost,

                      Ceil(Rand() * (10000 - 5000) + 5000) as [Additional Services]

                    While IterNo() <= 3;

                    LOAD * Inline [

                    Customer

                    A

                    B

                    C

                    D

                    E

                    F

                    G

                    H

                    I

                    J

                    K

                    L

                    M

                    N

                    O

                    P

                    Q

                    R

                    S

                    T

                    U

                    V

                    W

                    X

                    Y

                    Z

                    ];

                     

                    Dimension:

                    =Class(Aggr(Sum(Revenue)-Sum(Cost) - Sum([Additional Services]), Customer), 10000)

                     

                    Expression:

                    Count(DISTINCT Customer)

                      • Re: Class function
                        Prasanta Kumar
                        CountryCustomersRevenueCost1Cost2Cost3
                        IndOrang1004307
                        RusBharti20005454534
                        IndRel1001234
                        RusHP500541135
                        AmericaOrang234345433335
                        EMEABharti34237555343
                        PakRel64576344
                        BangHP5477343435345
                        AmericaKelvinator1004543
                        EMEAwirlpool200068743354
                        PakOrang1002334
                        BangBharti5002423223
                        EMEARel6000464322

                         

                         

                        I want to create a class for based on Revenue and Profit.

                         

                        and want to see customers at interval of 100.

                         

                        1st Bar chart

                        =====================

                        My dimension is Customer Name

                         

                        and measurement for Profit = sum(revenue)-(sum(cost1)+sum(cost2)+sum(cost3))

                         

                        My interval will be 100.

                         

                        2nd Bar chart

                        =======================

                         

                        Dimension customer name

                        Measurement - Sum(rev)

                         

                         

                        Thanks

                        Prasata

                          • Re: Class function
                            Prasanta Kumar

                            basically i want to see on bar chart, the customers with Gross profit interval of 0 to 100. 100 to 200 and so on.

                             

                            Simillary want to see customers on bar chart for based on total revenu with interval from 0 to 100, 100 to 200 and so on.

                            • Re: Class function
                              Sunny Talwar

                              Interval will be 100?

                              What does that even mean? I have no idea how to interpret that. Can you draw a picture by hand to show what is that you are looking for?

                                • Re: Class function
                                  Prasanta Kumar

                                  Actually on X axis of bar chart, i want to put the interval like, 0 to 100, 100 to 200, 200 to 300...and so on..

                                   

                                  and on Y axis, when i sum up the revenue for a particular customer, i will have the bar under respective interval..

                                   

                                  for example, sum of revenue for Orange is 2543, so the bar for orange should be drawn under interval 2500 to 2600.

                                   

                                   

                                  simillary, i want to calculate the profit and draw the bar for customer under respective intervals.

                                   

                                   

                                  is it possible?

                                   

                                  thanks

                                    • Re: Class function
                                      Sunny Talwar

                                      QlikSense isn't working right now, but this should work in Qlik Sense the same way:

                                       

                                      CHART1

                                       

                                      Dimension:

                                      =Class(Aggr(Sum(Revenue), Customers), 100)

                                       

                                      Expression:

                                      =Count(DISTINCT Customers)

                                       

                                      CHART2

                                       

                                      Dimension:

                                      =Class(Aggr(Sum(Revenue-Cost1-Cost2-Cost3), Customers), 100)

                                       

                                      Expression:

                                      =Count(DISTINCT Customers)

                                       

                                      Capture.PNG