topic Re: Value by Date Expression in QlikView

Value by Date Expression

Daz4 — Tue, 13 Dec 2022 03:42:23 GMT

I have two tables and need to work out the cost per hour. I cannot join by date for various reasons.

I have a Table using Date2 as Dimension and sum(Time) as the expression and need to bring in the cost as a second expression. The following expression does not work as it brings in no values, can you tell me why? I have tried reversing the Dates and also removing the sum.

=IF(DATE2=DATE1, sum(Cost))

Date1 Cost

1/01/2022 103

2/01/2022 558

...

Date2 Time

1/01/2022 2

2/01/2022 6

...

Re: Value by Date Expression

BrunPierre — Tue, 13 Dec 2022 13:16:37 GMT

=Sum(If(Date2=Date1,Cost))

Re: Value by Date Expression

Daz4 — Wed, 14 Dec 2022 01:35:29 GMT

Thank you that is exactly what I was looking for greatly appreciated.

I wonder if you could also assist on a change to that formula to get a sum of a date range?

I now have a table grouped by Monthname(Date1)

The following doesn't work:

=sum(IF(Date2>=MIN(Date1) andDate2<=MAX(Date1),Cost))

Re: Value by Date Expression

tresB — Wed, 14 Dec 2022 05:09:28 GMT

yes your expression would throw nested aggregation error. Try like:

=sum(IF(Date2>=MIN(Total Date1) and Date2<=MAX(Total Date1),Cost))

Re: Value by Date Expression

Daz4 — Wed, 14 Dec 2022 06:03:17 GMT

Thanks for your time on this.

It doesn't quite do what I want it to do as it returns the total for the entire date range as a single number summed up across the whole date range, not split into the months. So if Oct = $50k and Nov = $70, if I select one or the other they show correctly, but if I select both months then the same figure of $120k appear for each month. Any ideas how to get it working across each month?

Date Cost

Oct 50000

Date Cost

Nov 70000

Date Cost

Oct 12000

Nov 12000

Re: Value by Date Expression

tresB — Wed, 14 Dec 2022 06:52:05 GMT

One way could be like modifying the above expression like:

=sum(IF(Date2>=Aggr(MIN(Date1), MonthField) and Date2<=Aggr(MAX(Date1), MonthField),Cost))

Note: Use of aggr() would depend on where and how your are using this expression. I.e. - if you are using in chart, you might also need to include the chart dimensions in aggr() alongwith the month field.