topic Re: Values from hidden or excluded month in chart in QlikView

Values from hidden or excluded month in chart

Tue, 21 Apr 2015 07:16:45 GMT

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

Re: Values from hidden or excluded month in chart

Gysbert_Wassenaar — Tue, 21 Apr 2015 07:40:08 GMT

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

Re: Values from hidden or excluded month in chart

Tue, 21 Apr 2015 08:52:13 GMT

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

Re: Values from hidden or excluded month in chart

Gysbert_Wassenaar — Tue, 21 Apr 2015 08:59:49 GMT

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.

Re: Values from hidden or excluded month in chart

Tue, 21 Apr 2015 10:19:23 GMT

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?

Re: Values from hidden or excluded month in chart

Tue, 21 Apr 2015 13:58:08 GMT

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

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 06:54:59 GMT

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?

Re: Values from hidden or excluded month in chart

Gysbert_Wassenaar — Wed, 22 Apr 2015 07:30:43 GMT

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

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 09:20:02 GMT

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

Re: Values from hidden or excluded month in chart

Gysbert_Wassenaar — Wed, 22 Apr 2015 09:31:34 GMT

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.

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:02:17 GMT

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

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

Re: Values from hidden or excluded month in chart

Gysbert_Wassenaar — Wed, 22 Apr 2015 10:17:50 GMT

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.

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:17:53 GMT

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

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:21:30 GMT

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

the value

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

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:23:04 GMT

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

Re: Values from hidden or excluded month in chart

Gysbert_Wassenaar — Wed, 22 Apr 2015 10:30:26 GMT

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.

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:32:36 GMT

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

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:36:11 GMT

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

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 10:59:13 GMT

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)

Re: Values from hidden or excluded month in chart

Wed, 22 Apr 2015 11:13:54 GMT

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

The Reduce column is a hidden column of course