Count distinct is not working in script?

[#1]

Dear community,

I found problem for script below, the FlightNumber is always showing 1...

FlightNumber:

Load Fltno,

    [Flt Dt],

    Count(distinct Fltno) * Count(distinct [Flt Dt])) as FlightNumber

resident FlightData

group by [Flt Dt], Fltno;

I think count distinct might not work in this way in script, anyone knows to workaround to overcome this?

it is working fine in expression in chart.

For special reason, i cannot have expression in the chart, i need a (number of flight) field,

so that the chart can directly use the field.

(limitation of GeoQlik flow chart, only can use field, expression is not working)

I need the number of flight field can corresponding to the filter selection. just like the figure show in blue text box.

Thanks and best regards,

Chanel

[#2]

Hi Jagan,

Thanks for this hint, i should group by route and [Flt Wk Dt].

But I just realized my expression to calculate number of flights is incorrect.

As shown in attachment, for ADL-KUL route, there should be 8 flights but my expression result is 14 flights.

= count (distinct Fltno) * count (distinct [Flt Dt]) is not the correct formula...

Appreciate if you could edit my expression.

Thanks and best regards,

Chanel

[jagan · Accepted solution]

Hi,

Try any of this expressions

=Sum(Aggr(1, Fltno, [Flt Dt]))

or

= count(DISTINCT Fltno &  Ceil([Flt Dt]))

Regards,

Jagan.

[#4]

Awesome Jagan! Thanks Guru!