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

[Gysbert_Wassenaar]

I think the solution to your problem is to create an additional table in the script to link each month to itself and its previous month. This is variant of an AsOf table. You can find an explanation in this document: Calculating rolling n-period totals, averages or other aggregations

[#3]

Thanks Gysbert

I had a look at this.

First it is only required for one month, which probably makes it easier. but then the issue is with the second question, I may have 20000 transaction in the first month of the year, but in the prior month, there are 25000 records which need to be summed to get the total. I am not sure how you will be able to link these two tables. If I already had total tables with the same dimensions etc where I could do a straight link it would have been great.

Am I looking at this correctly that there will be an issue?

Andre

[Gysbert_Wassenaar]

I don't think there should be any issue. You'd create a table with records like below:

AsOf:

ReportMonth, Month, Type

03/01/2015,03/01/2015, Current

03/01/2015,02/01/2015, Previous

02/01/2015,02/01/2015, Current

02/01/2015,01/01/2015, Previous

01/01/2015, 01/01/2015, Current

01/01/2015, 12/01/2014, Previous

The Fact table is linked to the AsOf table on the Month field. Then you use ReportMonth as dimension instead of Month. For summing current values you use an expression like sum({<Type={'Current'}>} Value), for values of the previous month something like sum({<Type={'Previous'}>} Value)

Note, I used the start dates of the month in the example table above, but that isn't a requirement. I don't know what kind of month field your source table has.

[#5]

Thanks, it is just a FinancialPeriod, for example 201501, so your advice will actually work as well, i will test this and see how it works, as I stated before it is only one month's information I need for this so at least it will only be about 300000 extra rows in another table. I will letyou know as soon as I am able to test this.

One question though, if I just bring the sum through as a column and then hide the column and use the column as the calculation that would work fine as well?

[#6]

I have tried this but it's not working

Fact table i have HPeriod, HFinancialYearStatus , H120DaysPlus

Asof table I have HPeriod, HPPeriod, Type

HPeriod and HPPeriod format are year month, example 200501

My Calculated Dimension is

=if(HFinancialYearStatus = 1 and Type = 'Current',HPPeriod)

My Expression to get the Balance is

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

The Periods are 200407-200506, all of these have HFinancialYearStatus = 1

I have coded another field HFinancialYearStatus1 which has the same value for 200407-200506(value of 1), but has a value for 200406 of 10 - this is to differentiate it from the other statuses and to mark it as the prior period

The Expression wirh the set analysis above gives me a zero value. What else could I be doing to bring through the value?

Hope I have given through enough information

[#7]

Another option is marking 200406 as HFinancialYearStatus = 1, and then hiding the row?

Can I use a calculated dimension as well as hiding the first row (example where HPeriod = 200406. If this will work what should the exact syntax be.

I could then have a column handled by set analysis to only give the balance from 200406?

[Gysbert_Wassenaar]

Please read this document and post a sample qlikview document: Preparing examples for Upload - Reduction and Data Scrambling

[#9]

Hi

Attached is the model

If you have a look at Ageing History Performance 120Days + and the one below with the Test added on in the name - we need to reflect the Actual from 201406, this is then used with % Reduced to get to the Reduction column, which then is the amount by which the target decreases, The Actual in 201406 is used to calculate the reduction and target in  201407 and 201408 and month going ahead,

I excluded previous periods data.

The script is protected with password Qlikview

Note the top chart is how the information should look except that the reduction should be 99392 etc

[Gysbert_Wassenaar]

You created the fields with the names switched around. Try this:

NOCONCATENATE ASOF:

  SELECT DISTINCT [FinancialPeriod] HPPeriod

  ,[FinancialPeriod] HPeriod

  ,'Current' as Type

FROM [R_B_DebtorAccountServiceAgeingHistory] A

  JOIN [V_F_LedgerPeriod] P ON A.Period = P.FinancialPeriod

  JOIN [V_F_LedgerFinancialYear] F ON P.FinancialYear = F.FinancialYear

  Order by HPeriod;

CONCATENATE ASOF:

  SELECT Top 1  [FinancialPeriod] HPPeriod

  ,([FinancialPeriod] - 1) HPeriod

  ,'Previous' as Type

  FROM [R_B_DebtorAccountServiceAgeingHistory] A

  JOIN [V_F_LedgerPeriod] P ON A.Period = P.FinancialPeriod

  JOIN [V_F_LedgerFinancialYear] F ON P.FinancialYear = F.FinancialYear

  where FinancialYearStatus = 1;

Then use HPPeriod as chart dimension instead of HPeriod.