19 Replies Latest reply: Nov 8, 2012 11:08 AM by whiteline _ RSS

    Ignoring dimension in a chart

      I have a problem with a chart that i build.

      The chart has a line which monitors usage per day and accumalates this, so it is a (mostly) linear line upwards.

       

      I also have a formula which calculates the expected endpoint of this line so usage this month till today, defided by the amount of days this month till today, multiplied by the number of days in the month.

       

      third I have a formula which calculates the amount of data which is available to use. This is amount of users times the amount the user can use.

       

      These last two formulas are not within the dimension (not linked to days) and therefore does not display correctly. I would like to know if there is a adject for the expression which calculates the formula and uses only the result in the chart.

      If i use just a number as the expression, for instance 1203, it creates astraight line in the chart. So i want the expression to do just that, calculate the number and place it as a straight line.

       

      Kind regards,

        • Re: Ignoring dimension in a chart
          Mario De Stefano

          Hi,

          can you post a .qvw sample of problem?

          • Re: Ignoring dimension in a chart

            QV community.png

            The yellow and the green line have to become straight horizontal lines. The formula from yellow is ([usage till today] / [days till today]) * [days of the month]

            The problem is that it uses the dimension when i do not want it to. Normally the expression would return one value, now it returns values for each different day..

              • Re: Ignoring dimension in a chart
                Mario De Stefano

                You can try with Set Analysis:

                 

                Yellow Expression:

                 

                =(sum({$<Cdr_StartDay=>}[usage till today]) / sum({$<Cdr_StartDay=>}[days till today])) * sum({$<Cdr_StartDay=>}[days of the month])

                 

                 

                Hope this helps

                 

                Regards

                Mc

                  • Re: Ignoring dimension in a chart

                    Thank you mario,

                     

                    but this doesn't work, using "cdr_startday=" causes to ignore selections in the cdr_startday, not the dimension of the graph.

                     

                    Can it be so difficult? if i calculate the value in a table without dimension cdr_startday I get a value. If i type this value in the chart expressions there is a straight line...

                      • Re: Ignoring dimension in a chart
                        Mario De Stefano

                        Try to create 2 graphs and overlay them  , with transparent background for the second

                         

                        Fisrt graph=Dimension,Blue Line Expression

                        Second Graph = without dimension,Yellow Line Expression,Green Line Expression

                         

                        All with your formulas,without Set Analysis

                         

                        MC

                          • Re: Ignoring dimension in a chart

                            Thanks Mario,

                             

                            I tried to avoid that because at some point it will be messed up (selections, dragging, changing axis). But i guess i could try it..

                             

                            Still a other solution is welcome!

                              • Re: Ignoring dimension in a chart
                                jagan mohan rao appala

                                Hi,

                                 

                                Try

                                 

                                Suppose if you have Dimension1, Dimension2 then you can ignore Dimension2 by using

                                 

                                =Sum(TOTAL<Dimension2> Sales)

                                 

                                Regards,

                                Jagan.

                                • Re: Ignoring dimension in a chart
                                  whiteline _

                                  Hi.

                                   

                                  To discard all dimensions except Cdr_StartDay, try:

                                  =sum(total<Cdr_StartDay> [usage till today]) / sum(total<Cdr_StartDay> [days till today])) * sum(total<Cdr_StartDay> [days of the month])

                                    • Re: Ignoring dimension in a chart

                                      I know, but the intention is to ignore the dimension of the graph-> Cdr_StartDay. Not to ignore the others like you suggest. Thanks anyway:)

                                        • Re: Ignoring dimension in a chart
                                          whiteline _

                                          I don't see the names of other dimensions on screenshots

                                          I thought you read the help and substitute the correct dimension names

                                           

                                          You don't have to build monstrous expression using the above on top of yours.

                                          I suggested ir as an example as you've mentiond earlier: [usage till today] / [days till today]) * [days of the month]

                                           

                                          Take your expression, modify the aggregation functions with a total modifier and correct dimensions.

                                            • Re: Ignoring dimension in a chart

                                              you said it would discard all dimensions except Cdr_StartDay, i want it to discard Cdr_Startday.

                                               

                                              I try to work with the total, but i've never used it before and do not totally understand it..

                                               

                                              I feel like i am not able to explain my problem

                                               

                                              The graph is showing the total data usage from our clientbase till today day (blue)

                                              &

                                              The prediction of how much data they've used at the end of the month (yellow)

                                               

                                              The problem with the yellow expression is that it has different values per day, as the data usage per day varies. The only usable data is the prediction from today, the graph shows predictions for each single day(because the day is the dimension).

                                              The yellow line has to become a straight horizontal line, since the prediction for today is a single value.

                                              QV community2.png

                                              This is how i want the graph to be, so that we can manage availability and usage.

                                               

                                              If I enter 250.000 MB it gives me the straight line i want. Now i want the graph to use the outcome of the expression (250.000) and not the outcome per day(i.e. ignore the dimension).

                                               

                                              A possible solution is to refer to a cell in another table, would this be possible?

                                                • Re: Ignoring dimension in a chart
                                                  whiteline _

                                                  Could you create a table without Cdr_Startday dimension and calculate the desiered values ?

                                                  If so, post the corresponding expressions for prediction and availabale value.

                                                    • Re: Ignoring dimension in a chart

                                                      QV community3.png

                                                      Total usage:                          =sum(Cdr_DataKB)/1024

                                                      Average usage per day:                   =(column(1))/count(DISTINCT Cdr_StartDay)

                                                      prediction                                          =(column(2))*avg(monthdays)

                                                       

                                                      Column(3) = the yellow expression from before. This calculated value i want as a constant in the graph, this constant changes only on reload (daily). As you can see without dimension Cdr_StartDay it does the trick. So i want the graph to ignore the dimension for the last expression (prediction)

                                                       

                                                       

                                                        • Re: Ignoring dimension in a chart
                                                          whiteline _

                                                          So as you've used DataServiceType as dimmension use it in your expressions for line chart:

                                                          =sum(total<DataServiceType> Cdr_DataKB)/1024/count(DISTINCT total<DataServiceType> Cdr_StartDay)

                                                          =sum(total<DataServiceType> Cdr_DataKB)/1024/count(DISTINCT total<DataServiceType> Cdr_StartDay)*avg(total<DataServiceType> monthdays)

                                                • Re: Ignoring dimension in a chart

                                                  Ok, ive looked some more in the TOTAL function and it looks like it might be a solution. But I cannot get it to work.

                                                  for [usage till today] I use       =sum({<DataServiceTypeFull={"DBS1S"}>} Cdr_DataKB)/1024

                                                  for [days till today] I use         =count(distinct Cdr_StartDay)

                                                  for [days of the month] I use  =avg(Cdr_StartMonthDays)

                                                   

                                                  The expression in a bar chart I use is

                                                  =(sum({<DataServiceTypeFull={"DBS1S"}>} Cdr_DataKB))/1024 /(count(distinct Cdr_StartDay)))*avg(Cdr_StartMonthDays)

                                                  This works but it has DataServiceTypeFull as the dimension and no days.

                                                   

                                                  The expression you suggest would be

                                                   

                                                  =((sum(total<Cdr_StartDay> (sum({<DataServiceTypeFull={"DBS1S"}>}Cdr_DataKB)/1024)) / sum(total<Cdr_StartDay> count(distinct Cdr_StartDay)))*sum(total<Cdr_StartDay> avg(Cdr_StartMonthDays)))

                                                   

                                                  This expression does not return any data.. do you have an idea why?