Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
WernerDC
Creator
Creator

Qlik Sense Advanced Scripting

Hi guys, 

I have to develop the following App. We are an Agricultural firm.

It is a forward planning tool consisting of 1 Variable (Resources allocated)

1. We have Soil Analysis displaying in Hectares that need to be done before a certain time in a Year.

This is what I need to do:

     Start: 5000 Hectares (Week43)

     Resources: 500 Hectares per week. 

     I need to calculate when that Start Hectares will be done. In what week? The catch is that the recourses are not set in       stone and that it should be changeable when we see we will not finish in the set time.

 

So:

In week 42 we sit on 5000 Hectares. With recourses at 500 Hectares/ Week we will be finished with the work by week 51 where we will have no Hectares remaining. Here's the catch they maybe want the work to be done in week 49 so they need to send more recourses so the 500 Hectares/ Week should therefor change.

 

I hop someone can help me with this.

 

Kind regards

 

 

1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

Hi @WernerDC 

First of all, thank you for the sample data, it helped!

Second, when you reply to a topic in the Qlik Community you can type an @ it allows you to pick-up the account name of the people assisting you in that particular topic, so we get a promptly notification when you reply to us.

I made some changes to your Excel file and created a prototype of the application, you did not clarify about sharing this data here, or do you want them by private e-mail. I do not know if you need to comply with security protocols at your end, please clarify! 

These are some of the actions I had taken with your Excel file:

  • Added worksheet 'Table_1', which I believe naming it 'Resources' will be more meaningful for the solution.
  • Added worksheet 'Table_2', which is the SmartSheets data, I think you  will get this data directly from SmartSheets.
  • Added worksheet 'Variables', it define external variables required by the solution, at this stage there is only one variable, I named vProject_Weeks; you can not change this variable name because it is referenced by the script, but you can change its value, which is defined by the Expression column.

The application I wrote is still a little bit raw.

  • It only handles a single Country-Service combination, the presence of Country and Service makes me think your solution need to support multiple countries and services, e.g. it should do projections for many Services and Countries (please confirm)
  • I assigned the name 'Service' to the column containing the iTest Soil.
  • The application could be extended for multiple resource definitions per service and generate multiple projections; your current model feature a single resource definition per service which produce a single projection.
  • The application is static at the moment, what I mean by that is: 'It does not support future weeks' and you having define the expected projections behaviour for the 'following week'.

I am sending by email the QVF and Excel file, but I believe we should share them here in the topic; please clarify your preference.

Some screenshots:

02.Projection-01.png

02.Projection-02.png

02.Projection-03.png

Regards,

 

 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

5 Replies
Taoufiq_Zarra

@WernerDC  can you share a sample data and the expected output  ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
ArnadoSandoval
Specialist II
Specialist II

Hi @WernerDC 

Very interesting scripting challenge, before I proposed my ideas, would you be able to confirm clarify these assumptions:

  • Your problem is a projection into the future of the weekly resources required.
  • Is this projection for a single item (I would say field) or this is done for many?
  • If this is "projection into the future", would you be tracking its performance as time move forward? by your example, on week 43 you recorded 500 hectares, but in reality, it was recorded 450 hectares, and the model should adjust for the remaining time (I am thinking on how your projection adjust overtime).
  • You shared with us a constant weekly resources rate; Is the weekly resources always a constant?

With your current example, this formula tells you the time in weeks to run out of resources:

[Weeks Required] = [Target Hectares] / [Weekly Resource]

 Am I in the right track? Would you add/share more ideas?

Thanks in advance

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
WernerDC
Creator
Creator
Author

Hi Arnado, thanks for your insights.

  • Yes. I have to somehow (minus) the Hectares completed in week 42 from the Starting Hectares in week 42 and let that be the Start of week 43 etc.
  • This may vary.
  • So the Hectares done in a week MUST be changeable because we may add more resources(people) to up that Hectares done in a week from 500 to say 800 if we want to finish earlier. (Chip away at the Starting 5000 Hectares/Acres faster)
  • it is not. It will change.

Please attach your email so I can mail the Excel concept.

 

 

 

WernerDC
Creator
Creator
Author

Hi Taoufiq

Please attach your email so I can mail the Excel concept.

ArnadoSandoval
Specialist II
Specialist II

Hi @WernerDC 

First of all, thank you for the sample data, it helped!

Second, when you reply to a topic in the Qlik Community you can type an @ it allows you to pick-up the account name of the people assisting you in that particular topic, so we get a promptly notification when you reply to us.

I made some changes to your Excel file and created a prototype of the application, you did not clarify about sharing this data here, or do you want them by private e-mail. I do not know if you need to comply with security protocols at your end, please clarify! 

These are some of the actions I had taken with your Excel file:

  • Added worksheet 'Table_1', which I believe naming it 'Resources' will be more meaningful for the solution.
  • Added worksheet 'Table_2', which is the SmartSheets data, I think you  will get this data directly from SmartSheets.
  • Added worksheet 'Variables', it define external variables required by the solution, at this stage there is only one variable, I named vProject_Weeks; you can not change this variable name because it is referenced by the script, but you can change its value, which is defined by the Expression column.

The application I wrote is still a little bit raw.

  • It only handles a single Country-Service combination, the presence of Country and Service makes me think your solution need to support multiple countries and services, e.g. it should do projections for many Services and Countries (please confirm)
  • I assigned the name 'Service' to the column containing the iTest Soil.
  • The application could be extended for multiple resource definitions per service and generate multiple projections; your current model feature a single resource definition per service which produce a single projection.
  • The application is static at the moment, what I mean by that is: 'It does not support future weeks' and you having define the expected projections behaviour for the 'following week'.

I am sending by email the QVF and Excel file, but I believe we should share them here in the topic; please clarify your preference.

Some screenshots:

02.Projection-01.png

02.Projection-02.png

02.Projection-03.png

Regards,

 

 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.