Problem charting dataset with 2 dates in QlikView

[#1]

Hi,

I have what appears to be a simple charting requirement but I must confess I'm totally stumped. Any help would be gratefully appreciated.

I retrieve the following dataset from a SQL Server query (though the same issue occurs if I use an Excel spread sheet)

Sample data

Record Number	Date Raised	Date Actioned
1	11-Feb-2014	11-Feb-2014
2	11-Feb-2014	11-Feb-2014
3	11-Feb-2014	18-Feb-2014
4	11-Feb-2014	25-Feb-2014
5	18-Feb-2014	<null>
6	18-Feb-2014	18-Feb-2014
7	18-Feb-2014	25-Feb-2014
8	25-Feb-2014	25-Feb-2014
9	25-Feb-2014	<null>

Note: <null> values just haven't been set yet. "Date Raised" would never be <null>

When I try to produce a chart for this data I would like to see the following counts:

Date	Count(Date Raised)	Count(Date Actioned)
11-Feb-2014	4	2
18-Feb-2014	3	2
25-Feb-2014	2	3

When I chart "Date Raised" and "Date Actioned" separately I get the counts I was expecting.

However, When I create a chart including both counts I see totally different figures.

Example totals I get back from QlikView are:

Date	Count(Date Raised)	Count(Date Actioned)
11-Feb-2014	4	4
18-Feb-20143	3	2
25-Feb-2014	2	1

I have tried using different date combinations in the "Dimensions" but to no avail

Like many before, I am very new to QlikView and (again, like many before) am learning on the job

Thanks,

Michael

[sunilkumarqv]

Make changes to your Old Date into  new Date at script  level

=date(floor(date#([Old Date],'MM/DD/YYYY hh:mm:ss')),'MM/DD/YYYY') as [New Date]

Hope its helps

[#3]

Hi Sunil,

I'm not totally sure I understand what you are suggesting.

Are you suggesting that I apply this change to my SQL query or somewhere within QlikView?

If we are talking abut the SQL query then I didn't think you could use "floor" on a date (as it isn't a float). I have tried using the SQL Server command Convert(Date,<date>) but that didn't affect my QlikView output.

If the change is within QlikView, can you tell me where I would I would add this function?

Thanks,

Michael

[Gysbert_Wassenaar · Accepted solution]

One option is to use the crosstable function and use a pivot chart to show the data the way you want it. See attached example.

[CELAMBARASAN]

Here you have to do a different approach like

RawData:

Load

     RecNo,

     RaisedDate,

     ActionedDate

From SomeSource;

//or it might be from database SQL SELECT * FROM TableName

MasterTable:

Load

     RecNo,

     RaisedDate AS Date,

     'Raised' AS Status

Resident RawData;

Concatenate(MasterTable)

Load

     RecNo,

     ActionedDate AS CommonDate,

     'Actioned' AS Status

Resident RawData

Where not IsNull(ActionedDate);

Then use CommonDate as dimension in chart

Count({<Status={'Raised'}>}Distinct RecNo) AS Raised count Expression

and

Count({<Status={'Actioned'}>}Distinct RecNo) AS Actioned count Expression

Hope it helps

Celambarasan

[#6]

In the load script I would create a flag :- if(Date Raised = Date Actioned,1,0) as flag.

Then simply sum the flag in the chart...

HTH

[#7]

Big thanks to the guys who replied.

I must confess that I didn't try all of the options presented

Celambarasan Adhimulam - Not sure I followed your answer, but that is purely a sign of my inexperience of QlikView