Dividing a Field value by another field which has a different date.

[Danqlik]

Hi All,

I am trying to create a variable calculation which takes the bad debt for the month and turns in into a percentage the total cumulative expected revenue three months prior to that date. e.g: vBadDebtRate(%) = Bad Debt (t0)/Cumulative expected revenue (t-3)

where t = month.

I have attached an excel highlighting what i am trying to achieve. The column highlighted in yellow is the variable calculation i would like to achieve and the other three columns would be raw data inputs.

Any Advice?

Thanks greatly in advance,

[brunobertels]

Hi 

Try this 

[Table]:
load *,

rowno() as LoadOrder,
date#([Date],'MM/YY') as Date1,

if(isnull(peek([Cumulative Expected Collection],-3)),'',
[Bad Debt]/peek([Cumulative Expected Collection],-3)) as [Bad Debt Rate]
;

LOAD * INLINE
[
Date;Bad Debt;Cumulative Expected Collection
janv-21; - ; 616 148,89
févr-21; - ; 2 561 391,60
mars-21; - ; 4 654 463,86
avr-21; 37 198,77 ; 6 620 356,30
mai-21; 96 954,10 ; 8 730 345,69
juin-21; 236 456,01 ; 10 683 210,71
juil-21; 359 385,89 ; 12 792 766,87
août-21; 512 909,57 ; 14 747 127,51
sept-21; 651 548,55 ; 16 863 428,20
oct-21; 788 473,99 ; 18 809 981,92
nov-21; 915 966,56 ; 20 928 351,01
déc-21; 1 013 993,83 ; 22 882 725,03
janv-22; 1 099 357,48 ; 24 983 902,19
févr-22; 1 178 974,02 ; 26 922 600,89
mars-22; 1 243 103,19 ; 28 948 102,48
avr-22; - ; 30 762 743,33
mai-22; - ; 32 569 264,08
juin-22; - ; 34 165 975,49
juil-22; - ; 35 627 196,35
août-22; - ; 36 922 671,25
sept-22; - ; 38 149 854,43
oct-22; - ; 39 234 870,99
nov-22; - ; 40 183 688,97
déc-22; - ; 40 935 148,54
janv-23; - ; 41 519 717,33
févr-23; - ; 41 927 917,52
mars-23; - ; 42 212 553,17
avr-23; - ; 42 359 488,00
mai-23; - ; 42 418 464,76
juin-23; - ; 42 437 374,22
juil-23; - ; 42 437 507,99
août-23; - ; 42 437 507,99
sept-23; - ; 42 437 507,99
oct-23; - ; 42 437 507,99
nov-23; - ; 42 437 507,99
déc-23; - ; 42 437 507,99
](delimiter is ';');

output : 

[Danqlik]

Hi, 

Thanks so much that works! One added question if you dont mind. Is this something that can only be produced in the load script? As opposed to say a variable after the raw data has been loaded? I ask because when i tried to create it as a variable, my app would not recognise 'peek' as a function and only did so in the loading phase.

I am pretty new to Qliksense so not sure about all the functionality and efficiency of certain capabilities within the app.

Thanks in advance!