QlikView App Dev

[qvraj123]

Hi All,

Thanks for your time, I have this scenario where I get the detail data in an excel file - I need to generate the projections data based on the average of the licenses up until Dec 2020 -- I have the detail data in the following format

Detail Data

I need to get the sum of column D and an accumulation for the same in a different column

Column C is the cumulative value, Column D is the cost per server or license which is column C

The cost is known variable for example for 2017 and 2018 it is 14,000, for 2019 it is 11,200 and for 2020 it is 8400

Total allowed is also a known variable and it is constant up until 2020

Aggregated Data by Month

This is the data that is available in until March 2018 in the detail data - starting from April 2018 is the projections data

This is based on the average of column B in the above - i.e average of Sum of Licenses -- in this case it is 11

so using the average of 11 from April 2018 to Dec 2020 I have these below projections

when I get the actual data for April 2018 - instead of the average 11 I should take the actual value that is coming from the data

and take the average again including April 2018 then assign that value from May 2018 to Dec 2020

projections data - this is something that I need to generate then combine the actual and projections

once I get the data I need to show the data by month with cumulative licenses, Column C and Column D - with values on 2 axes 1 for licenses and 1 for total cost

could any please provide suggestions on how to generate the projections data based on the average - average should be based on the data that is available as of in this case data available is as of March 2018

thanks a lot for your time

[qvraj123]

anyone? if the post is not clear please tell me -

[t_chetirbok]

Hi Raj

Can you send your source file? As I understand SampleFile.xls is the data that you want to have as a result

[qvraj123]

Hi Tatsiana, Thank you so much,

Yes, the attached includes the data - the Sheet1 includes the detail data

The data in sheet3 -  This includes the detail data that is aggregated at the Month level, with cumulative column, total cost,  For the years 2017, 2018 the cost is 14,000, for 2019 11,200 and for 2020 it is 8,200

Now I have to take the avg and use that average value starting from April 2018 so that the calculations work

Assuming 11 is my average for the above data - assign 11 starting from April 2018 to end of Dec 2020

when I get the May 2018 data i.e. the actual data that will be part of detail data in Sheet1 I have to  take the average as of May 2018 - so on we need to add the most recent month of data available to get the average

Thank you so much for your time

[t_chetirbok · Accepted solution]

I attached the file with my solution

[qvraj123]

Hi Tatsiana, Thank you so much; this is really helpful,

I have provided the sample that I used for my graph - I see you are generating the dates and assigning the avg value, last cummu value before concatenating - thanks a lot