my columns do not calculate with dates

[#1]

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

[swuehl]

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)))

[#3]

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

[swuehl]

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

[#5]

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.

[swuehl]

One thing that is jumping out:

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

[#7]

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.

[swuehl]

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)

[#9]

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

[swuehl · Accepted solution]

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