topic Re: Total wrt dates in pivot table in QlikView

Total wrt dates in pivot table

priya945 — Sat, 16 Nov 2024 19:22:08 GMT

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

Re: Total wrt dates in pivot table

Anil_Babu_Samineni — Wed, 01 Jan 2020 10:04:28 GMT

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

Re: Total wrt dates in pivot table

priya945 — Wed, 01 Jan 2020 16:55:14 GMT

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

Re: Total wrt dates in pivot table

Kushal_Chawda — Wed, 01 Jan 2020 17:14:07 GMT

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

Re: Total wrt dates in pivot table

priya945 — Wed, 01 Jan 2020 18:01:24 GMT

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

Re: Total wrt dates in pivot table

Kushal_Chawda — Wed, 01 Jan 2020 18:07:23 GMT

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

Re: Total wrt dates in pivot table

priya945 — Thu, 02 Jan 2020 03:53:09 GMT

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

Re: Total wrt dates in pivot table

Brett_Bleess — Tue, 21 Jan 2020 20:27:49 GMT

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