Forum Discussion
two date slicers
ddtblar,
Could you please share sample data of your tables and post expected result based on sample data here?
Besides, why not create a calendar table, and then create relationship between calendar table and invoice date table, create relationship between calendar table and paid date table? This way, you can create a slicer using date field of the calendar table to filter visuals.
Regards,
Lydia
I have 2 table just like this:
- Invoice data
CUSTOMER_ID | STORE_ID | REFNBR | AMOUNT | DUEDATE |
M0028 | TP1 | 027842 | 50,000.00 | 15-Apr-18 |
G0061 | TP1 | 34122 | 100,000.00 | 20-Apr-18 |
D0087 | TP2 | 274105 | 80,000.00 | 15-Feb-18 |
M0028 | TP1 | 338465 | 65,000.00 | 01-Jul-18 |
E0001 | TP3 | 25788 | 35,000.00 | 30-Sep-17 |
- Paid data
CUSTOMER_ID | STORE_ID | REFNBR | AMOUNT | PAID_DATE |
M0028 | TP1 | 027842 | 50,000.00 | 30-Apr-18 |
G0061 | TP1 | 34122 | 100,000.00 | 20-May-18 |
D0087 | TP2 | 274105 | 80,000.00 | 16-Apr-18 |
M0028 | TP1 | 338465 | 65,000.00 | 01-Aug-18 |
E0001 | TP3 | 25788 | 35,000.00 | 30-Aug-18 |
I wanna know the AR (account receivable) aging based on the due date. there are 4 categories which are:
- aging 0-30 days
- aging 31-60 days
- aging 61-90 days
- aging more than 90 days
So when the first date slicer is set to 1 august 2018, all duedate invoice transactions for the period of July should be aggregated in a column called 'aging 0-30 days', for duedate in June, which is more than 30 days from 1 august 2018, should be aggregated in a column called 'aging 31-60 days', etc.
In report should show table like this:
AR aging invoice
STOREID | AGING 0-30 | AGING 31-60 | AGING 61-90 | AGING >90 |
TP1 |
|
|
|
|
TP2 |
|
|
|
|
TP3 |
|
|
|
|
For paid table, it is linked with invoice table, so i want to show aging paid table based on duedate in invoice table. Basically the second report table is just like the first table in AR aging invoice, but the categories are follow the duedate, not the paid date. The second slicer in here is use to set the date based on paid date.
Example, the second slicer is set to 31 august 2018. So all data in paid table which more than that is filtered, only the data that below 31 august 2018 are selected. Then, the paid data should be aggregated in a column called 'aging 0-30 days', and other categories based on the invoice just like report table in AR aging invoice
AR aging paid
STOREID | AGING 0-30 | AGING 31-60 | AGING 61-90 | AGING >90 |
TP1 |
|
|
|
|
TP2 |
|
|
|
|
TP3 |
|
|
|
|
I already made two calendar tables, but i cant make this scenario, can you help me?
Thanks. (sorry for my bad english)
hey guys,
I need help about using two date slicer to know the aging of account receivable.
I have 2 table just like this:
- Invoice data
CUSTOMER_ID
STORE_ID
REFNBR
AMOUNT
DUEDATE
M0028
TP1
027842
50,000.00
15-Apr-18
G0061
TP1
34122
100,000.00
20-Apr-18
D0087
TP2
274105
80,000.00
15-Feb-18
M0028
TP1
338465
65,000.00
01-Jul-18
E0001
TP3
25788
35,000.00
30-Sep-17
- Paid data
CUSTOMER_ID
STORE_ID
REFNBR
AMOUNT
PAID_DATE
M0028
TP1
027842
50,000.00
30-Apr-18
G0061
TP1
34122
100,000.00
20-May-18
D0087
TP2
274105
80,000.00
16-Apr-18
M0028
TP1
338465
65,000.00
01-Aug-18
E0001
TP3
25788
35,000.00
30-Aug-18
I wanna know the AR (account receivable) aging based on the due date. there are 4 categories which are:
- aging 0-30 days
- aging 31-60 days
- aging 61-90 days
- aging more than 90 days
So when the first date slicer is set to 1 august 2018, all duedate invoice transactions for the period of July should be aggregated in a column called 'aging 0-30 days', for duedate in June, which is more than 30 days from 1 august 2018, should be aggregated in a column called 'aging 31-60 days', etc.
In report should show table like this:
AR aging invoice
STOREID
AGING 0-30
AGING 31-60
AGING 61-90
AGING >90
TP1
TP2
TP3
For paid table, it is linked with invoice table, so i want to show aging paid table based on duedate in invoice table. Basically the second report table is just like the first table in AR aging invoice, but the categories are follow the duedate, not the paid date. The second slicer in here is use to set the date based on paid date.
Example, the second slicer is set to 31 august 2018. So all data in paid table which more than that is filtered, only the data that below 31 august 2018 are selected. Then, the paid data should be aggregated in a column called 'aging 0-30 days', and other categories based on the invoice just like report table in AR aging invoice
AR aging paid
STOREID
AGING 0-30
AGING 31-60
AGING 61-90
AGING >90
TP1
TP2
TP3
I already made two calendar tables, but i cant make this scenario, can you help me?
Thanks. (sorry for my bad english)