Very Interesting task for scripting

[rassanbekov]

Hello, guys!

Very intersting task for Qlikview scripting.

I have data set for ''project code'', "hours", "rate"  and "realization" rate.

The income is recognized when hours x rate x realization.

However, the realization changes over time.

When the realization changes the income is recongized by last available realization.

The formula should aggregate the value like "hours" ((1) x "rate" (1) + "hours" (2) + "rate" (2)) x realization (2). something like this.

I include the input data. Please propose any script.

Kind regards,

Ruslan

[vinieme12]

Would you be able to post some dummy data that best represents your scenario and the expected output?

[vinieme12]

How to get answers to your post?

Qlik Community Tip: Posting Successful Discussion Threads

[rassanbekov]

Inputting data.

The output should look like

Job code	Month	Result
123	June	5 x 10 000 x 30%
456	June	6 x 10 000 x 30%
789	June	7 x 10 000 x 30%
123	July	(5 x 10 000 + 8 x 10 000) x 40%
456	July	(6 x 10 000 + 10 x 10 000) x 40%
789	July	(7 x 10 000 + 11 x 10 000) x 40%

[rassanbekov]

posted

[effinty2112]

Hi Ruslan,

Data:

LOAD Month,

     Employee,

     [Job code],

     HRS,

     Rate

FROM

data.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Month,

     [Job code],

     Realization

FROM

[data 2.xlsx]

(ooxml, embedded labels, table is Sheet2);

Job code	Month	HRS*Rate*Realization/100
123	June	15000
123	July	32000
456	June	18000
456	July	40000
789	June	21000
789	July	44000

Regards

Andrew

[rassanbekov]

Thank Andrew. But the task is not about simple multiplication.

The formula to be written in a way to add up previous monthes and current month and multiply total to current realization rate.

It reminds me aggregation function. But I cannot set up correct formula.

Please consider that option.

Kind regards,

Ruslan

[effinty2112]

Hi Ruslan,

     Sorry - did not read carefully enough. will respond

Andrew

[Peter_Cammaert]

Your expression could be something like (this is in the UI, the script can be built using GROUP BY and a similar expression)

=Sum(HRS * Rate) * FirstSortedValue(Realization, -RealizationMonthStart)

The only thing you need to add when reading the Realizations, is to convert the month (in what year is this month anyway) into a MonthStart() date value and call it RealizationMonthStart.

Best,

Peter

[rassanbekov]

Thank you, Peter!

I changed input "month" into date format. And loaded script as MonthStart (Month) into script editor. Then I just copied your expression but it didn't work out.

Is it possible if you share qvw file, where you test your expression?

Kind regards,

Ruslan