I'm new in qlik and I'm trying to accomplish the following request with no luck, I hope someone can give some ideas:
My issue is having a % value calculated based 2 columns by Year and Week:
Year Week Value 1 Value 2 %
year week sum(x) sum(y) [Value 1] / [Value 2]
The results having year and week in the chart works fine, but now I would like to create a second chart having only Year and % where the percentage is not really the natural aggregation of this value, i would like to have an average of the results.
having this data:
| Year | week_id | KeyId | Total Misc Sales | Total Dept Sales | % |
| Year FY16 | 201648 | 4 | $19,583.85 | $2,209,132.25 | 0.89% |
| Year FY16 | 201649 | 4 | $0.00 | $5,157,718.87 | 0.00% |
| Year FY16 | 201650 | 4 | $286.20 | $4,360,959.95 | 0.01% |
| Year FY16 | 201651 | 4 | $8.50 | $3,736,121.04 | 0.00% |
| Year FY16 | 201652 | 4 | $7.99 | $1,308,088.04 | 0.00% |
| | | $19,886.54 | $3,354,404.03 | 0.12% |
I would like to get
| Year | KeyId | % |
| Year FY16 | 4 | 0.18% |
Where 0.18% is the Average of % values divided of number of weeks
But Qlik will give me:
| Year | KeyId | % |
| Year FY16 | 4 | 0.12% |
Any ideas will help
Thanks
Darlene
