Qlik sense - Totals and Averages

[beginnerqliksen]

Dear Forum members,

I am fairly new to Qlik sense, I am stuck in an aggregation or grouping issue (I think), I wanted to display the data as per the below view:

I am struggling with last three columns highlighted in orange.

First Issue

I wanted to show the total sales figure for the country excluding the sales for that customer, for example, Customer account “ABCD” has done 1200 pre and 1300 post, total sales done pre Date_Check i.e 2017-04 (pre 3 months) was 31200 so the value should appear as 30000 (please see first row) and post sales respectively:

I have a date check which is a used for evaluating each customer performance post and pre 3/4/5/6 months. For explaining my problem I am only considering 3 months post and pre sales/averages. I am able to calculate correctly Pre and post sales and averages for each customer based on Date_Check but unable to calculate last two columns. I have created a column “Date_Mapped_ID” which is a pointer to select my range (Record ID) from 2017-01 to 2017-12 values starts from 1 to 12 respectively, as below, I have sales figures by month for 2017 for each customer.

Second issue

“% Growth” (Third last column) –is showing the total based on the total which using formula:

(Post Avg – Pre Avg)/Pre Avg

Whereas if You see below the average for Country code “US”, instead of -3.49 it is showing based on the totals which is -8.39 :

   

Any pointers to the query will be appreciated, many thanks in advance.

[sunny_talwar]

Would you be able to share the app where we can see the issue?

[beginnerqliksen]

Hi Sunny,

Thank you very much for your response, Please see attached, I have attached a QVF and just for the safe side the data I prepared for sample.

I have two tabs on the app - the first one is the desired/required output with some hard-coded values in the last 5 columns of VIEW 1 and VIEW 2 and the second tab is the one I am struggling with, I have tried to simplify the data as much as possible in order to understand my both issues.

I hope this makes sense, Please feel free to raise any further questions.

[sunny_talwar]

Hey Asim -

It took me a while to understand, but I know what you are looking to get. But this won't be easy (if not impossible) to do this directly on the front end of the app. Are you open to modification in the script?

Best,

Sunny

[beginnerqliksen]

Hi Sunny,

Thanks for the prompt response, I can try and see if that helps.

Kind regards,

[sunny_talwar]

Sounds good, will get back to you as soon as I have the back end based solution.

[sunny_talwar]

Do you need this value to change based on selections? or is this static value which will never change? Talking about Business Pre-Sale

[beginnerqliksen]

Thank You so much, appreciated Your help and support.

[beginnerqliksen]

Hi Sunny, Thank You very much for your help and support, Yes it will be dynamic, so the number of the months depending on the selection of the user in order to calculate the business pre/post.

Once again thanks for looking into this.