Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Values from hidden or excluded month in chart

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

22 Replies
Not applicable
Author

Carrying on from Capture2, I created an extra column called Previous1 with the expression

If(RowNo() = 1, (Bottom(Previous)), Above(Previous1))

My issue now is how to hide the row without dropping the data, if I can do that I can do my calculations from the Previous1 Column

Not applicable
Author

This what I have right now

Password for script is Andre

I need to hide the row, not suppress it, with the null in PeriodNo without losing the data in Previous and Previous1 columns as I use that to Calculate the Reduce Column

Not applicable
Author

Solved, showed a variable number of rows depending on how many period of the year have passed