22 Replies Latest reply: Apr 24, 2015 7:35 AM by Andre Toerien RSS

    Values from hidden or excluded month in chart

    Andre Toerien

      Hi

       

      I have the following situation.

       

      I am using a calculated dimension in a chart to only bring through the current year's data. I however need to use the total from the previous month in calculations, this total can be changed by selections made on the model.

       

      The way I see it there are a few options, but none of them seem to work for me. I really need help finding a solution.. I have tried all of these , maybe I am just doing something wrong

       

      One option would be to include the prior month's data but then to hide the row.

      The I tried variables, which really did not work out at all for me.

      I have a text box which has the value which changes the value as you make selections, but I see a lot on the forum where guys say you cannot really bring back the value from a text box into a chart.

       

      what needs to happen in effect is that I have a a few columns with data that need to reflect for the current financial year. I have a target field which needs to be calculated from the total for the previous month. so say we need to bring the outstanding balance down by 1% per month from the balance of the previous month. The prior month which we do not want to show has a balance of 10000, the balance for the 1st month of the new year is 11000, but the target is 10000 - 1%. We also need a column that gives us the amount it should be decreasing per month, which will stay constant, example 100, but the target bakance column then should be 9900, the month after that 9800 etc

       

      so you would have something like this

       

      (hidden value 10000)

      month, balance, target amount,target total,variance

      July,11000,100, 9900, target total/balance%

      August,11500,100,9800, target total/balance%

      September, 10000,100,9700, target total/balance%

       

      Hope this makes sense