Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Expert
I have calendar and transaction table as below.
I have filter of Month (Mth) filed in report.
I have to calculate the sum values in SET theory formula like sum({<FiscalYrMth={P3YrMth}>}Sales). eg If i click "Jan' in mth field i would get three possible values in 'P3YrMth' field (201401,201402,201403) so i have to calculate previous three month (incuding current month) values.
Calendar Table:
FiscalYrMth | P3YrMth | Mth | MthCode | Year | Quarter |
201401 | 201311 | Nov | 1 | 2014 | Q1 |
201401 | 201312 | Nov | 1 | 2014 | Q1 |
201401 | 201401 | Nov | 1 | 2014 | Q1 |
201402 | 201312 | Dec | 2 | 2014 | Q1 |
201402 | 201401 | Dec | 2 | 2014 | Q1 |
201402 | 201402 | Dec | 2 | 2014 | Q1 |
201403 | 201401 | Jan | 3 | 2014 | Q1 |
201403 | 201402 | Jan | 3 | 2014 | Q1 |
201403 | 201403 | Jan | 3 | 2014 | Q1 |
201404 | 201402 | Feb | 4 | 2014 | Q2 |
201404 | 201403 | Feb | 4 | 2014 | Q2 |
201404 | 201404 | Feb | 4 | 2014 | Q2 |
201405 | 201403 | Mar | 5 | 2014 | Q2 |
201405 | 201404 | Mar | 5 | 2014 | Q2 |
201405 | 201405 | Mar | 5 | 2014 | Q2 |
201406 | 201404 | Apr | 6 | 2014 | Q2 |
201406 | 201405 | Apr | 6 | 2014 | Q2 |
201406 | 201406 | Apr | 6 | 2014 | Q2 |
201407 | 201405 | May | 7 | 2014 | Q3 |
201407 | 201406 | May | 7 | 2014 | Q3 |
201407 | 201407 | May | 7 | 2014 | Q3 |
201408 | 201406 | Jun | 8 | 2014 | Q3 |
201408 | 201407 | Jun | 8 | 2014 | Q3 |
201408 | 201408 | Jun | 8 | 2014 | Q3 |
201409 | 201407 | Jul | 9 | 2014 | Q3 |
201409 | 201408 | Jul | 9 | 2014 | Q3 |
201409 | 201409 | Jul | 9 | 2014 | Q3 |
201410 | 201408 | Aug | 10 | 2014 | Q4 |
201410 | 201409 | Aug | 10 | 2014 | Q4 |
201410 | 201410 | Aug | 10 | 2014 | Q4 |
201411 | 201409 | Sep | 11 | 2014 | Q4 |
201411 | 201410 | Sep | 11 | 2014 | Q4 |
201411 | 201411 | Sep | 11 | 2014 | Q4 |
201412 | 201410 | Oct | 12 | 2014 | Q4 |
201412 | 201411 | Oct | 12 | 2014 | Q4 |
201412 | 201412 | Oct | 12 | 2014 | Q4 |
TransTable:
FiscalYrMth | Sales |
201401 | 100 |
201402 | 200 |
201403 | 300 |
201404 | 400 |
201405 | 500 |
201406 | 600 |
201407 | 700 |
201408 | 800 |
201409 | 900 |
201410 | 1000 |
201411 | 1100 |
201412 | 1200 |
201501 | 1300 |
201502 | 1400 |
201503 | 1500 |
201504 | 1600 |
201505 | 1700 |
201506 | 1800 |
201507 | 1900 |
201508 | 2000 |
201509 | 2100 |
201510 | 2200 |
201511 | 2300 |
201512 | 2400 |
Hi,
You should link the table using P3YrMth from table 1 and FiscalYrMth from table 2.
Regards,
Kaushik Solanki
Hi Ramu,
check this Topic, that can contribute to solve your issue:
Qlikview Last 7 Days Sales by RFB 223 - YouTube
i hope that helps
beck
On a similar note, look at The As-Of Table to get more idea of how this can be done.