topic Re: Values Calculation Issue in QlikView

Values Calculation Issue

Tue, 26 Jun 2012 14:19:27 GMT

Hi everybody,

i have issue to calculate and display fields from an already loaded table (input_table).

Please find attached in Excel a sample of the input data and the output as i want it to be calculated.

I have already got informations in the input table from the source table using a load. Then i am doing a 2nd load to get informations as in output table, but i am blocked.

My purpose is make histogramm chart based on the quarterly

thank you

Re: Values Calculation Issue

giakoum — Tue, 26 Jun 2012 14:41:18 GMT

UPDATED!!

please check attached file.

solution is chart on the right or so I think

in order to get correct results, change Quarter to 2011 1 instead of 1 2011, otherwise it is difficult to order it correctly.

Re: Values Calculation Issue

christian77 — Tue, 26 Jun 2012 15:16:00 GMT

In that case you are gonna have to add a cero

right('0' & month, 2)

2012 10 and 2012 1 will not order correctly.

luck.

Re: Values Calculation Issue

Tue, 26 Jun 2012 15:16:44 GMT

Thank you loannis for your answer,

Your script gave me more informations but i don't know i you got my challenge. Maybe i was not very clear and i am sorry for that. So let me explain with more details .

Px is a project, and for each project i have the actual turnover YTD each month. I have already done a processing to get values at end of quarter (i took value on march, june, september and december). then i got the values in the input file.

Now what i want is for each project, to have the turnover generated within the quarter. That means, for a specific project i should have the values for Q1, Q2, Q3 and Q4. These values are different from the actual turnover witch is a photo of the present.

So here is the rule to get these quarterly values( as calculated in the OUTPUT TABLE):

To get "quarterly Q1": i take the actual turnover in march (with represents the the 1st quarter) QUARTERLY Q=ACTUAL Q1

To get "quarterly Q2": i substract actual in june from actual Q1 (actual Q2- actual Q1 ) Quarterly Q2= ACTUAL Q2 -ACTUAL Q1

To get "quarterly Q3": i substract actual in september from actual Q2 (actual Q3- actual Q2 ) Quarterly Q3= ACTUAL Q3 -ACTUAL Q2

To get "quarterly Q4": i substract actual in september from actual Q3 (actual Q4- actual Q3 ) Quarterly Q4= ACTUAL Q4 -ACTUAL Q3

NOTE: All my input values are ACTUAL values and i am trying to calculte QUATERLY Values

Hope i have been more clear and thank you for your help Ioannis

Regards

Re: Values Calculation Issue

giakoum — Tue, 26 Jun 2012 20:34:13 GMT

Thank you Christian, you are perfectly right!

Macsigarev :

I hope I got it right this time. I thought Px was period... Please check. Sorry for the delay, I was offline.

BR, Ioannis.

Re: Values Calculation Issue

Wed, 27 Jun 2012 13:30:44 GMT

thank you Ioannis,

That's Great.

1) But is it possible to have the Quarterly (as a graphic in your report) in the original tab(where i have the cumulated YTD) ? Then the quaterly will be just an additionnal field!

2 ) Then can i write the below script at the load stage ?

if(isnull(Above([cumulated turnover YTD])) or Left(Quarter,4)<>Above(Left(Quarter,4)), [cumulated turnover YTD], [cumulated turnover YTD] - Above([cumulated turnover YTD]))

I am asking you these questions because my purpose is to do a Histogramm after i get the values you have calculated in the "Quarterly" graphic !

Regards

Cherif

Re: Values Calculation Issue

giakoum — Wed, 27 Jun 2012 15:07:55 GMT

Hi Cherif

It is just another field in any chart you make. Please see attached. Hope I understood well!

You can use the same expression in load script, but must replace above with previous and be very cautious how to sort the load statement, so that previous returns correct results.

BR, Ioannis.

Re: Values Calculation Issue

Wed, 27 Jun 2012 15:21:15 GMT

All right Ioannis, really thank you for your help.

But how can do a histogramm chart based on the data displayed in the graphic you did ?

Regards

Re: Values Calculation Issue

giakoum — Wed, 27 Jun 2012 16:02:19 GMT

Something like the attached?

Do you have a sample?

Re: Values Calculation Issue

Thu, 28 Jun 2012 14:38:00 GMT

Hi Ioannis,

sorry i was not at my desk today, that why i didn't answer you.

Please find attached the type of histogramm i want!

Here My dimensions are:

-The quarter

-A ratio in percentage calculated as following ([quarterly]/[cumulated turnover YTD]).

So depending of the value it will have a percentage range (<10%;10-20%;20-30%;30-40%;>40%)

Best Regards

cherif