topic my columns do not calculate with dates in QlikView

my columns do not calculate with dates

Tue, 21 Feb 2012 20:50:42 GMT

I am trying to get a running total between dates and the date below function does not work - suggestions? Please see attached picture

Re: my columns do not calculate with dates

swuehl — Tue, 21 Feb 2012 20:56:13 GMT

Are these dates dimension values? You can't use column(3) or column(2) function to access them there, I believe, try using the dimension. What are your dimensions and complete expressions? Could you upload a small sample?

Hope this helps,

Stefan

edit:

If you are using the dates as dimensions, try something like

=(DateA-below(total DateB))

and if you get in trouble at the column segments, maybe like

if(below(total Record)=Record,(DateA-below(total DateB)))

Re: my columns do not calculate with dates

Tue, 21 Feb 2012 21:08:25 GMT

Hello, thanks for responding regarding dimension - I tried as dimension and got the following

=day(interval([Column (3)]) - below([Column (2)]))

I get an error ??Error in calculated dimension

Re: my columns do not calculate with dates

swuehl — Tue, 21 Feb 2012 21:11:02 GMT

Can you open qvw-files or are you on personal edition?

Re: my columns do not calculate with dates

Tue, 21 Feb 2012 22:11:39 GMT

Thank you for helping and responding so quickly. I am sooo frustrated. To test your equation, I exported about 6 records and loaded in new .qvw file and put in your equation and changed to my fields

if(below(total Record)=Record,(Admitted-below(total Discharged))) and got a good result.

I then went to my original file (which is very large) and did a copy and paste and the results do not come back, all I get is a -.

I am stumped, does not make sense. I can't really send you the file, too large. I am sending some images to see if it helps.

my columns do not calculate with dates

swuehl — Tue, 21 Feb 2012 22:14:45 GMT

One thing that is jumping out:

Your first dimension is Patient ID instead of Record, you need to adapt the expression accordingly.

my columns do not calculate with dates

Tue, 21 Feb 2012 22:21:44 GMT

Great catch and yes I changed but no result - again, sorry and thanks for the help. So frustrating when something so simple is taking so many hours.

Re: my columns do not calculate with dates

swuehl — Tue, 21 Feb 2012 22:27:57 GMT

Ah, I see, your patient IDs change in every line, I assumed that you want to calculate your difference only for the same Record / Patient ID. If a change in patient ID is nothing we need to worry about, just remove the if() clause:

=Admitted-below(total Discharged)

edit:

Or use only Patient ID as dimension, then three expressions:

=Admitted

= Discharged

=Admitted-below(Discharged)

my columns do not calculate with dates

Tue, 21 Feb 2012 22:29:56 GMT

No, I need it to change according to the specific record (patient) - I am calculating how long before they come back into the hospital.

Re: my columns do not calculate with dates

swuehl — Tue, 21 Feb 2012 22:31:53 GMT

Then try to sort by Patient ID or use a pivot table which will automatically group by your dimension.

Re: my columns do not calculate with dates

Tue, 21 Feb 2012 22:48:43 GMT

Thanks for all your help!! It finally fixed when I actually put the medical record # in the table and removed the patient ID and the calcs appeared when I changed to if(below(total MR#)=MR#,(Admitted-below(total Discharged)))

Again thank you.