Hello Dears,
Grateful for your help to build a qliksense formula for a bar chart with Dimension = Item name / description
Please note that my raw excel data will contain only columns A to I but not the last columns J.
I have included column J here in order to show the calculation which i need to implement in qliksense, that is first i need to calculate column G in qliksense for each row of item data. This i have already done and is working.
| Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H | Column I | Column J |
| Country Code | Country | Item name / description | Year | Month | Period id | Total Stock End Of Curr Month | Total Stock Start Of Curr Month | Total Qty Shipped | Abs(G - H)
+
I |
| AUS | Australia | Cable samsung N10 | 2020 | 1 | 202001 | 6700 | 0 | 3700 | 10400 |
| AUS | Australia | Cable samsung N10 | 2020 | 2 | 202002 | 8200 | 6700 | 4300 | 5800 |
| AUS | Australia | Cable samsung N10 | 2020 | 3 | 202003 | 1200 | 8200 | 5400 | 12400 |
| AUS | Australia | Cable Apple A20 | 2020 | 1 | 202001 | 5300 | 0 | 1800 | 7100 |
| AUS | Australia | Cable Apple A20 | 2020 | 2 | 202002 | 2400 | 5300 | 3300 | 6200 |
| AUS | Australia | Cable Apple A20 | 2020 | 3 | 202003 | 3500 | 2400 | 1100 | 2200 |
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
| When user selects month = 3 in qliksense, display 2 item bars: | Bar 1: | 0.468531469 | =SUM(I3+I4+I5)/SUM(J3+J4+J5) | | | |
| | | | | | | | | | |
| | | | | Bar 2: | 0.722580645 | =SUM(G6+G7+G8)/SUM(J6+J7+J8) | | | |
Users Requirement (upon dynamic selection of year and month):
In the filters when the user selects Year = 2020 and Month 2, the bar chart should display 2 bars with results cumulated for month 1 and 2 inclusive
In the filters when the user selects Year = 2020 and Month 3, the bar chart should display 2 bars with results cumulated for month 1 till month 3 inclusive.
That is when user selects month 3, there will be 2 bars displayed as follows:
Bar 1 for 'Cable samsung N10' calculated cumulatively for month 1 till 3 inclusive with result 0.46
Bar 2 for 'Cable Apple A20' calculated cumulatively for month 1 till 3 inclusive with result 0.72
(Note:The excel file is attached)
I have tried to implement the bar chart with Dimension = Item name / description and i would like you to check if the formula below is correct? Thanks for confrming if it is ok.
=
Aggr(sum({$<Month= {">=0"}, Month = {"<= $(=Max(Month))"} >} [Total Qty Shipped]), Year, Month, [Item name / description])
/
(
fabs(Aggr(sum({$<Month= {">=0"}, Month = {"<= $(=Max(Month))"} >} [Total Stock End Of Curr Month]) , Year, Month, [Item name / description])
-
Aggr(sum({$<Month= {">=0"}, Month = {"<= $(=Max(Month))"} >} [Total Stock Start Of Curr Month]), Year, Month, [Item name / description])
)
+
Aggr(sum({$<Month= {">=0"}, Month = {"<= $(=Max(Month))"} >} [Total Qty Shipped]), Year, Month, [Item name / description])
)
