Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

my columns do not calculate with dates

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

10 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

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

Not applicable
Author

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.dates.jpg

swuehl
MVP
MVP

One thing that is jumping out:

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

Not applicable
Author

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

swuehl
MVP
MVP

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)

Not applicable
Author

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

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