Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My data is as below
Case Number | Region | Closed Date | Opened Date |
1234 | North | 30-08-2018 | 30-08-2018 |
1243 | North | 30-09-2018 | 30-09-2018 |
1245 | South | 31-10-2018 | 31-10-2018 |
1254 | South | 30-08-2018 | 30-08-2018 |
1123 | East | 30-09-2018 | 31-10-2018 |
1232 | West | 31-10-2018 | 31-10-2018 |
expected o/p as below where i need Total with respect to dates. please guide
Total | North | South | East | West | |||||||||||
30-08-2018 | 30-09-2018 | 31-10-2018 | 30-08-2018 | 30-09-2018 | 31-10-2018 | 30-08-2018 | 30-09-2018 | 31-10-2018 | 30-08-2018 | 30-09-2018 | 31-10-2018 | 30-08-2018 | 30-09-2018 | 31-10-2018 | |
count of case number | 2 | 2 | 2 | 1 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 |
Thanks In advance
For me this is straight forward, But output is not correct for me as per your needs. Can you share output of below data?
Case Number | Region | Closed Date | Opened Date |
1234 | North | 30-08-2018 | 30-08-2018 |
1243 | North | 30-09-2018 | 30-09-2018 |
1245 | South | 31-10-2018 | 31-10-2018 |
Hi Anil,
Please find below expected output where i need count of case number for all Regions. Also Total (count of CaseNumber for all Regions) wrt to date
Total | |||
30-08-2018 | 30-09-2018 | 31-10-2018 | |
count of case number | 2 | 2 | 2 |
Just do below change in your Script
Use Date(Date#([Opened Date],'DD-MM-YYYY'),'M/D/YYYY')
instead of Date(Date#([Opened Date],'D-M-YYYY'),'M/D/YYYY') for both Dates
Now create Pivot table
Dimension:
'Count of case Number'
NDate
Expression:
Count(distinct [Case Number])
Drag NDate field to column in Pivot table
Also, check 'Suppress when value is NULL; option for NDate
and check 'Suppress zero value' from presentation tab
Hi Kush,
PFA for test data.
issue is to get Total(sum of all regions) as below in qlikview respecting the dates
Total | |||
30-08-2018 | 30-09-2018 | 31-10-2018 | |
count of case number | 2 | 2 | 2 |
Thanks in advance
Just do below change in your Script
Use Date(Date#([Opened Date],'DD-MM-YYYY'),'M/D/YYYY') instead of Date(Date#([Opened Date],'D-M-YYYY'),'M/D/YYYY') for both Dates
Now create Pivot table
Dimension:
'Count of case Number'
Region
NDate
Expression:
Count(distinct [Case Number])
Drag Region & NDate field to column in Pivot table
Also, check 'Suppress when value is NULL; option for NDate
and check 'Suppress zero value' from presentation tab
Hi Kush,
Thanks for your reply but i am unable to get the desired o/p following your steps . Could please find attached qvw file where Total value needs to breakdown to date
Thanks
Priya, just going to leave a comment to pop this back up in the list to see if Kush or anyone else may have some ideas given you attached a QVW file.
Regards,
Brett