Total wrt dates in pivot table

[priya945]

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

[Anil_Babu_Samineni]

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

[priya945]

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

[Kushal_Chawda]

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 

[priya945]

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

[Kushal_Chawda]

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 

[priya945]

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

[Brett_Bleess]

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