topic Re: Find a value when 2 dates match in QlikView

Find a value when 2 dates match

bronsonelliott — Tue, 03 Feb 2015 19:34:11 GMT

I'm having trouble figuring out the proper syntax for a formula and looking for some help. In my data model, I have data that looks similar to below. I have multiple metrics each one having different amounts of history (CLNDR_DT column) as well as different timing of when the data was last refreshed (AS_OF_DATE column.

What I am trying to do is display this data in a straight table but I want 1 line per metric and the value to be the Value where the CLNDR_DT = AS_OF_DATE as represented in the green rows below.

So the straight table would look something like this:

I have tried the following formulas and I can't get them to do what I am wanting. They seem to be looking at the max AS_OF_DATE for all metrics and not keeping it metric specific.

sum({<CLNDR_DT = {AS_OF_DATE}>} Value)

sum({<CLNDR_DT = {Max(AS_OF_DATE)}>} Value)

sum({<CLNDR_DT = {"$(=Max(AS_OF_DATE))"}>} Value)

I hope this was clear enough. What am I missing?

Re: Find a value when 2 dates match

Anonymous — Tue, 03 Feb 2015 21:22:55 GMT

Try using if():

sum(if(floor(CLNDR_DT) = floor(AS_OF_DATE), Value))

(I added floor() here in case the dates are actually timestamps)

Re: Find a value when 2 dates match

jonasheisterkam — Tue, 03 Feb 2015 21:37:32 GMT

- Value / (not CLNDR_DR-AS_OF_DATE)

Re: Find a value when 2 dates match

rwunderlich — Tue, 03 Feb 2015 22:45:47 GMT

Playing off what others have suggested.

=sum(Value * (CLNDR_DT = AS_OF_DATE) * -1)

=sum({<CLNDR_DT={"=CLNDR_DT=AS_OF_DATE"}>}Value)

If it's always the highest CLNDR_DT rows you want for each Metric:

=FirstSortedValue(Value, - CLNDR_DT)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Find a value when 2 dates match

bronsonelliott — Tue, 03 Feb 2015 22:50:33 GMT

Thank you everyone. I actually went in a slightly different direction. I'm curious what you think.

I actually made a calculated dimension like the following:

=if(CLNDR_DT = AS_OF_DATE, Metric)

Seems to be working for me.

Re: Find a value when 2 dates match

rwunderlich — Tue, 03 Feb 2015 23:44:03 GMT

Interesting. I would not have thought of that one. The only downside I can see is if you want to add another expression that processes multiple rows, like Avg(Value). But it actually makes it easier to add other dimension values from the same row. So that's a plus.

Glad you got it working and contributed to your own question!

-Rob

Re: Find a value when 2 dates match

Anonymous — Wed, 04 Feb 2015 14:22:10 GMT

This is fine. In fact I typically recommend calculated dimensions if there are multiple expressions in chart (see my post here http://community.qlik.com/thread/150574). If there is only one, I'd rather change the expression.