topic Re: Chart function in QlikView

Chart function

vignesh_s — Wed, 25 Nov 2020 16:16:04 GMT

Hi all i have problem in chart calculation

for 1st row of calculation i.e., for date 28/12/18 which is a dummy record joined with the fact table so for

each fund(a,b,c....) the 28/12/17 were added, for all funds with 28/12/17 as 1st row and calcualtion i need is (Qty/nv)*Rate and

from 2nd row and above (above of calulation field) /(1+Coresponding level field values)

ex:

Date Qty/Nv level Rate Value(Calculation)

28/12/18 11.2 3 11.2*3=33.6 (only for 1st row i.e., for date 28/12/18 calculation (Oty/nv)*Rate)

27/12/18 22.2 40 4 33.6/(1+40)=0.81 from 2nd and above the calculation is (Above(Value)/(1+coresponding level)

26/12/18 32 50 6 0.81/(1+50)=0.66 continue as above..........

..... 60

...

'Value' field is an expected output

Re: Chart function

Anil_Babu_Samineni — Fri, 12 Jan 2018 10:42:47 GMT

Better if you provide sample?

Re: Chart function

vignesh_s — Fri, 12 Jan 2018 10:42:51 GMT

the table is nt propor i thnk , i will repost it

ex:

Re: Chart function

vignesh_s — Fri, 12 Jan 2018 10:43:37 GMT

ya i will

Re: Chart function

Anil_Babu_Samineni — Fri, 12 Jan 2018 10:45:08 GMT

Please provide sample data? At least few rows in excel

Re: Chart function

vignesh_s — Fri, 12 Jan 2018 11:00:11 GMT

here i attached the excel file of it,

Re: Chart function

Anil_Babu_Samineni — Fri, 12 Jan 2018 11:07:22 GMT

How long this rows got Break? What if you have 100 Rows with Different Rows? Can u explain relate the Live data as well..

Re: Chart function

vignesh_s — Fri, 12 Jan 2018 11:16:08 GMT

Actually i cant tel the exact data its confidential ,i have fund field wchic conatind 120 funds in it ,each fund end with 28/12/17

so for each fund for date 28/12/18 calculation must be Price *star ,the date is arranged in ascending order

from 27/12/18 the calculation is

i.e.,previous value of Calculation part divided by (1+coresponding level value )

its quit triky i thnk,but hope u understoad my requiment now atlest

Re: Chart function

Anil_Babu_Samineni — Fri, 12 Jan 2018 11:38:18 GMT

We can use If..Else condition, But it's long process to achieve. Instead if you make 10 Rows and expected result that would give right way..

Re: Chart function

vignesh_s — Fri, 12 Jan 2018 11:56:37 GMT

ok i have attached excel with 10 records can u give me the solution,i want expression for chart

Re: Chart function

Anil_Babu_Samineni — Fri, 12 Jan 2018 13:40:20 GMT

Is this you are expecting?

Re: Chart function

shiveshsingh — Sat, 13 Jan 2018 10:31:47 GMT

try this..

Test:LOAD * INLINE [

Date, Qty/Nv, level, Rate

28/12/18 , 11.2, , 3

27/12/18 , 22.2, 40, 4

];

new:

load Date, [Qty/Nv], level,

Rate,RowNo() as Flag

Resident Test

order by Date desc;

drop table Test;

Final:

load Date,level,[Qty/Nv],Rate, if(Flag=1, [Qty/Nv]* Rate,Previous(([Qty/Nv]* Rate))/(1+level)) as Calculation

Resident new;

drop table new;

Re: Chart function

vignesh_s — Sun, 14 Jan 2018 08:17:27 GMT

exactly

Re: Chart function

vignesh_s — Mon, 15 Jan 2018 06:22:01 GMT

Can i get the expression fr this?

and sorry for late reply

Re: Chart function

vignesh_s — Mon, 15 Jan 2018 06:39:39 GMT

Thanks for ur reply bbut this is nt my exceptation

Re: Chart function

vignesh_s — Mon, 15 Jan 2018 11:38:22 GMT

hi can u please share ur expression please

Re: Chart function

Anil_Babu_Samineni — Mon, 15 Jan 2018 12:47:10 GMT

I haven't tried this in my QVW? I've applied this in excel itself. But, I tried now. For me it is not working. But interesting to follow.

Note - If possible, Try to make that calculation field in excel itself rather Qlik. Anyway, We would love to understand the scenario. Let's see if stalwar1‌ helps..

Re: Chart function

sunny_talwar — Mon, 15 Jan 2018 13:02:16 GMT

May be like attached

Re: Chart function

Anil_Babu_Samineni — Mon, 15 Jan 2018 13:11:15 GMT

Haha, Sunny i forget to sort Text for date field? That is the reason the values are not visible. Liked It !!!

Re: Chart function

sunny_talwar — Mon, 15 Jan 2018 13:46:49 GMT

Yes, sorting of the chart will be a key here