Solved: date(floor not working - Qlik Community

TomBond77 · ‎2023-02-16

Hi experts

We have to calculate the day difference between two date fields:

sum(date(floor([Code Date] - [Posting Day]))+1 ), but the result with "sum" is incorrect.

With this formula date(floor([Code Date] - [Posting Day]))+1 the result is correct:

But we need the "sum" for further ongoing calculations, without having the dates in the drill down. Any ideas?

hic · ‎2023-02-16

When the expression is evaluated, the engine loops over all combinations of [Code Date] and [Posting Day]. You seem to have two combinations, both evaluated to 378, so the sum is 756. Is this an error in the data model?

If this is the data model you want, you will need another expression, e.g.
Sum(Aggr(Avg(date(floor([Code Date] - [Posting Day]))+1),X))

This will loop over X, calculate the Avg, and then sum the result of each iteration. The question is only: What is X? It could be IssueNo or some other primary key.

View solution in original post

hic · ‎2023-02-16

When the expression is evaluated, the engine loops over all combinations of [Code Date] and [Posting Day]. You seem to have two combinations, both evaluated to 378, so the sum is 756. Is this an error in the data model?

If this is the data model you want, you will need another expression, e.g.
Sum(Aggr(Avg(date(floor([Code Date] - [Posting Day]))+1),X))

This will loop over X, calculate the Avg, and then sum the result of each iteration. The question is only: What is X? It could be IssueNo or some other primary key.