Values from hidden or excluded month in chart

[#1]

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

[#2]

It is still not giving me a balance in the Previous column

On Wed, Apr 22, 2015 at 11:31 AM, Gysbert Wassenaar <

[Gysbert_Wassenaar]

That's very strange. As far as I can tell it works in the example I attached in my previous post. I have no ideas anymore. Your qlikview app is violating the laws of nature of the universe I live in.

[#4]

It is showing the value in the one column on the Test chart, if I just use

HPPeriod as dimension, however if I use =if(HFinancialYearStatus =

1,HPPeriod) as dimension then it dows not show anything, it is also only

showing the data in the one row

See attachment

[#5]

it is also summing the actual incorrectly in 201407, basically duplicating

the value

On Wed, Apr 22, 2015 at 12:18 PM, Gysbert Wassenaar <

[#6]

Can you send me yours, please have a look at the other reply, it is calculating incorrectly in the one field as well

[Gysbert_Wassenaar]

Of course it doesn't show anything. There's no data for those periods that meet your HFinancialYearStatus filter requirements.

As you can see in the bottom right table, only HPeriod 201406 has records where HFinancialYearStatus is 10.

[#8]

I think the first concern for me is that the Actual Totals are out on your model.

Then issue two is I need to have the expressin to only bring back the current financialm year so only 201407 and 201408 must show, - that is why I use the calculated dimension =if(HFinancialYearStatus = 1,HPPeriod)

The other issue is that I need that value which is correct in that one column to be used to calculate Reduction and Target columns

[#9]

Makes much more sense already, sorry for wasting your time, but this one floors me

Now what I need is only the CurrentFinancial year to show i.e. 201407 and 201408, but the value in the previous column must be used to calculate Reduction and Target columns for both 201407 and 201408

[#10]

If I use calculated dimension =if(HFinancialYearStatus = 1,HPPeriod) it gives me Capture2.png

It then shows me 201407 and 201408 and then an extra line with the correct value in previous.It is easy to hide that line, but how do I get the value to calculate the Reduce column

I tried both of these

(sum({<Type={'Previous'}>} H120DaysPlus)* %Reduced)

(sum(Previous)* %Reduced)

[#11]

Gysbert, really hope you can take me through the rest of the calculations.

The Reduce column is a hidden column of course