Hoping someone could point me in the right direction of how to best do what I'm after.
1. I have some monthly data:
Date, ID_Type1, ID_Type2, Type2_Name & Value
2. I need to show (a) a summary and (b) a granular aggregation by date and type 2 id. For each month, I additionally need to know for each of the entries based upon ID_Type1 and ID_Type2 if it is NEW, EXISTING or DELETED for the month (compared to the previous) - so I need the deletions too to explain the moves. I have previously done this in excel VBA, but would like to see if we can use Qlik for such reporting directly from the data itself.
Based upon my example data, the summary table I would like is:
| Date | Value | Change Value MoM | Change EXISTING | Change NEW | Change DEL |
| 31/12/2020 | 159,423,689 | 159,423,689 | 0 | 159,423,689 | 0 |
| 31/01/2021 | 207,373,078 | 47,949,388 | 47,634,882 | 9,792,231 | -9,477,725 |
| 28/02/2021 | 140,117,086 | -67,255,991 | -45,275,790 | 7,340,156 | -29,320,358 |
| 31/03/2021 | 167,903,135 | 27,786,049 | 30,948,698 | 7,084,940 | -10,247,589 |
| 30/04/2021 | 119,671,023 | -48,232,113 | -36,746,296 | 2,939,899 | -14,425,715 |
And some of my granular output is:
| Date | ID_TYPE2 | TYPE 2 NAME | Value | Change Value MoM | Change EXISTING | Change NEW | Change DEL | Absolute Change Value MoM |
| 30/04/2021 | 0000000003 | NAME 3 | 68,254,921 | - 26,327,819 | - 24,192,126 | - | - 2,135,693 | 26,327,819 |
| 30/04/2021 | 0000000135 | NAME 135 | 472,651 | - 11,091,161 | - 9,921,515 | 4,434 | - 1,174,080 | 11,091,161 |
| 30/04/2021 | 0000000004 | NAME 4 | 8,951,205 | 6,539,633 | 7,110,851 | - | - 571,218 | 6,539,633 |
| 30/04/2021 | 0000000110 | NAME 110 | 863 | - 6,415,304 | - 6,415,304 | - | - | 6,415,304 |
| 30/04/2021 | 0000000125 | NAME 125 | 5,187 | - 5,700,826 | - 3,478,680 | 449 | - 2,222,595 | 5,700,826 |
| 30/04/2021 | 0000000089 | NAME 89 | 4,450,843 | - 4,604,171 | - 4,604,355 | 269 | - 85 | 4,604,171 |
| 30/04/2021 | 0000000103 | NAME 103 | 774,977 | - 3,357,742 | - 2,741,695 | 742 | - 616,790 | 3,357,742 |
| 30/04/2021 | 0000000009 | NAME 9 | 49,277 | - 1,847,990 | - 810,319 | 39,389 | - 1,077,059 | 1,847,990 |
| 30/04/2021 | 0000000104 | NAME 104 | 3,624,610 | - 1,813,771 | - 1,813,771 | - | - | 1,813,771 |
| 30/04/2021 | 0000000140 | NAME 140 | 37,671 | - 1,744,666 | - 963,384 | - | - 781,282 | 1,744,666 |
| 30/04/2021 | 0000000197 | NAME 197 | 4,761 | - 1,507,931 | - 1,507,931 | - | - | 1,507,931 |
| 30/04/2021 | 0000000189 | NAME 189 | 1,485,442 | 1,287,816 | 1,287,816 | - | - | 1,287,816 |
| 30/04/2021 | 0000000132 | NAME 132 | 411,107 | - 1,239,838 | - 1,241,268 | 1,514 | - 83 | 1,239,838 |
| 30/04/2021 | 0000000093 | NAME 93 | 1,199,670 | 1,038,801 | 1,038,833 | - | - 31 | 1,038,801 |
| 30/04/2021 | 0000000137 | NAME 137 | 1,396 | - 914,958 | - 914,958 | - | - | 914,958 |
| 30/04/2021 | 0000000010 | NAME 10 | 56,704 | - 907,379 | - 908,197 | 841 | - 23 | 907,379 |
Can anyone point me in the right direction on how to do this? Should I be doing any of this on loading?
I'm currently trying to evaluate Qlik for future use, so any help would be greatly appreciated. I couldn't see how to saved Qlik files, so some text answers would be appreciated.
