topic Re: Distinct values at max date in QlikView

Distinct values at max date

Hoowling — Sat, 16 Nov 2024 00:36:00 GMT

Hi community,

In a chart, I want to calculate the "planned qty" per VKRIDN (order number) at the max date.
An example of the raw data is found in below table.

Basically I want the chart to show 576 units on 2020-07-01.
Currently I'm using this formula: Sum(aggr(sum(Distinct[Planned qty]),VKRIDN)) - which puts the quantity at 2020-06-30.

Any suggestions on how to include a max(Date) function in the set analysis above?

Regards,
Hoowling

Re: Distinct values at max date

sunny_talwar — Wed, 27 May 2020 11:13:43 GMT

May be this

Sum(Aggr( If(Date = Max(TOTAL <VKRIDN> Date), Sum([Planned qty])) , VKRIDN, Date))

Re: Distinct values at max date

Hoowling — Wed, 27 May 2020 14:25:55 GMT

That worked when no date selections are made, but when I select a particular month the max date changes.
So if I select June, the planned qty of 576 appears in June, whereas if I select July the qty appears in July.
In this case I want the planned qty to be frozen to July (i.e. max date).

Regards,
Hoowling

Re: Distinct values at max date

vamsee — Wed, 27 May 2020 18:30:11 GMT

Try

Sum({<Date={"$(=Date(Max(Date)))"}>}[Planned qty])

Note: If you have Date as a dimension in the chart, for all the records it would show the latest value only.

Re: Distinct values at max date

sunny_talwar — Wed, 27 May 2020 18:45:18 GMT

I think it would be more like this

Sum({<Date = {"$(=Date(Max({1} Date)))"}>} [Planned qty])

Re: Distinct values at max date

Hoowling — Thu, 28 May 2020 08:36:47 GMT

Vamsee - This had no effect, the planned qty end up in the month that I select instead of just appearing in July (thus, should be zero if June is selected).

Re: Distinct values at max date

Hoowling — Thu, 28 May 2020 08:37:58 GMT

Sunny - This alternative returns null

Re: Distinct values at max date

sunny_talwar — Thu, 28 May 2020 11:20:05 GMT

Would you be able to share a sample where we can see the issue?

Re: Distinct values at max date

Hoowling — Tue, 02 Jun 2020 11:24:43 GMT

Attached is a very simple example to highlight the problem.
Again - I want the table to always show the planned qty @ max date. So if I select 20200630 planned qty should be 0, and when selecting 20200701, planned qty should be 576

Regards,
Hoowling

Re: Distinct values at max date

vamsee — Tue, 02 Jun 2020 12:50:59 GMT

Hi Hoowling,

I tweaked the expression a little, created a variable vMaxDate and used that in the expression.

Re: Distinct values at max date

tresB — Tue, 02 Jun 2020 13:05:38 GMT

May be you need implicit set like:

Sum({<Date*={"$(=Max({1} Date))"}>} [Planned qty])

Note the '*', intersection operator in red.