Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following tables
ReportingVersion
Reporting Date | cobDate |
01-Aug-2020 | 01-Jun-2020 |
01-Aug-2020 | 01-Jul-2020 |
01-Aug-2020 | 01-Aug-2020 |
01-Sep-2020 | 01-Jul-2020 |
01-Sep-2020 | 01-Aug-2020 |
01-Sep-2020 | 01-Sep-2020 |
CobDateDetails
cobDate | Dept | Status | Amount |
01-Jun-2020 | Dept1 | PASS | 1000 |
01-Jun-2020 | Dept2 | FAIL | 2000 |
01-Jun-2020 | Dept3 | PASS | 1500 |
01-Jul-2020 | Dept1 | PASS | 2000 |
01-Jul-2020 | Dept2 | PASS | 1000 |
01-Jul-2020 | Dept3 | PASS | 2500 |
01-Aug-2020 | Dept1 | PASS | 1500 |
01-Aug-2020 | Dept2 | FAIL | 2000 |
01-Aug-2020 | Dept3 | PASS | 1500 |
01-Sep-2020 | Dept1 | PASS | 500 |
01-Sep-2020 | Dept2 | FAIL | 700 |
01-Sep-2020 | Dept3 | PASS | 1500 |
The user would be making a reporting version selection . Based on the selection i want previous 2 months rolling to be calculated and shown (i.e)
If the user chooses 01-Sep-2020 as the version i want the result to be
Reporting Date | cobDate | Amount | Comments |
01-Sep-2020 | 01-Jul-2020 | 10000 | Should sum Jun + Jul |
01-Sep-2020 | 01-Aug-2020 | 10500 | Should Sum Jul + Aug |
01-Sep-2020 | 01-Sep-2020 | 7700 | Should sum Aug + Sep |
for 01-Aug-2020 it would be
Reporting Date | cobDate | Amount | Comments |
01-Aug-2020 | 01-Jun-2020 | 4500 | would be only sum june data |
01-Aug-2020 | 01-Jul-2020 | 10000 | Should sum Jun + Jul |
01-Aug-2020 | 01-Aug-2020 | 10500 | Should Sum Jul + Aug |
Can someone please let me know how to achieve this functionality .
Hi @kvrqliks
You should be able to achieve this by creating a separate table that links the right reporting dates to the correct cobDates.
I think you need another field, say cobGroup to use as the dimension, then link cobGroup to the right cobDates, so each cobGroup would link to two cobDates. The Reporting Date would then link to the correct cobGroups, so you're just adding another level to the joins you currently have.
This blog post talks about aggregating values in that way:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/
Hope that helps.
Steve
thanks Steve. This really helped . Apologies for the delay
Hi @kvrqliks
You should be able to achieve this by creating a separate table that links the right reporting dates to the correct cobDates.
I think you need another field, say cobGroup to use as the dimension, then link cobGroup to the right cobDates, so each cobGroup would link to two cobDates. The Reporting Date would then link to the correct cobGroups, so you're just adding another level to the joins you currently have.
This blog post talks about aggregating values in that way:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/
Hope that helps.
Steve
thanks Steve. This really helped . Apologies for the delay