topic Re: Evolutionary rate in QlikView

Evolutionary rate

marwen_garwachi — Sat, 16 Nov 2024 04:20:36 GMT

Hello,

I would like to calculate a recovery rate whose formula seems simple: "amounts recovered" / "Capital to recover" the problem is that I have to display the evolution of this rate in time knowing that the recovered amounts evolve in time and the same for the capital to recover.

Example: -

01-2018: Recovered = 50 € / capital = 1,000 € -> rate = 5%

-02-2018: Recovered = 100 € / capital = 1,000 € -> rate = 10%

-03-2018: Recovered = 150 € / capital = 1.500 € -> rate = 10%

If I activate a filter on the invoice creation date = 01-2018 I have to see only the invoices created in 01-2018 as well as the evolution of the receipts related to these invoices. What should be the dimension in this case? Date payment or date creation invoice?

Thank you in advance for your help

Re: Evolutionary rate

marwen_garwachi — Tue, 26 Feb 2019 12:13:36 GMT

Re: Evolutionary rate

tincholiver — Tue, 26 Feb 2019 14:22:39 GMT

Hi Marguen,

depends on the analysis you want to do you can use another or both dates as dimension.
If you want to know how much is recovered by the invoices issued in January for expamle, you should use as dimension the date of invoice, if you want to know in what date you recover the money you would use the recovery date, you can also know how long it takes to recover the money from an invoice issued using the two dates.

Do you want to share data to be able to see the case better?

Re: Evolutionary rate

marwen_garwachi — Tue, 26 Feb 2019 14:56:14 GMT

Hi,

I just want to see the evolution of the Recovery Rate in the time knowing that the capital to recover is inscreasing each week. The end user should be able to Focus on a spécific Month (for exemple 01-2018) and see the evolution of the recovery rate until 01-2019. And he should be able to see the Global Revocery rate (Global Capital to recover until today /Global Recovered until today) if he don't use any filter.

I want to show a trend with the recovery rate but i have tow possible dimension which are Payment Date and Invoice Creation Date and i'm trying to find a way to use both of them in the dimension.

I can't share data but you'll find attached the ERD, i hope it will help.

Thanks

Marwen

Re: Evolutionary rate

naveenboss — Wed, 27 Feb 2019 05:56:41 GMT

Hello... I am facing one problem in replacing field values. This is my data.

Month	City	SalesPerson
Jan	London	Vidyuth
Feb	Ny	Sambranth
Mar	Durban	Vidyuth
Apr	London	Sambranth
May	Ny	Vidyuth
Jun	Durban	Sambranth
Jul	London	Vidyuth
Aug	Ny	Sambranth
Sep	Durban	Vidyuth
Oct	London	Sambranth
Nov	Ny	Vidyuth
Dec	Durban	Sambranth

i want to Change the field vales of SalesPerson into NewSalesPerson. Only for Particular City and SalesPerson.

City	SalesPerson	NewSalesperson
London	Vidyuth	Indraban
Ny	Vidyuth	Jerrin
Durban	Sambranth	Jerrin

Re: Evolutionary rate

marwen_garwachi — Wed, 27 Feb 2019 11:54:17 GMT

Hi,

I don't know if i understand well what you want to do but i think you can use somethin like this:

IF (Match(City,'Ny','Durban') , 'Jerrin' ,

if(City = 'London', 'Indraban' , SalesPerson))

I hope it will help 🙂

ps: I think it's better to create a new subject for your problem 😉

Regards,

Marwen

Re: Evolutionary rate

naveenboss — Fri, 22 Mar 2019 05:35:07 GMT

i used mapping load. the script is below. Can i use like this.

NewSalesperson:
mapping
LOAD City&'-'&SalesPerson as Key,
New
FROM
Data1.xlsx
(ooxml, embedded labels, table is Sheet2);

Salesperson:
LOAD Month,
City,
SalesPerson,
ApplyMap('NewSalesperson',City&'-'&SalesPerson,SalesPerson/) as New
FROM
Data1.xlsx
(ooxml, embedded labels, table is Sheet1);