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.
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.
“% 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.
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.
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?