topic Re: Mode() Function for 1 record in QlikView

Mode() Function for 1 record

cloud — Sat, 16 Nov 2024 20:42:00 GMT

Hi experts,

I want to get the mode for the name for the current month, but for the current month, it has only 1 existing record. This is my expression that displays the name in a textbox in Qlikview:

Mode({<Month ={"$(=vCurrentMonth)"}>} Name)

The result that it shows for that current month is blank ( is it because it's returning NULL?)

Would like it to display the name regardless of whether it has 1 or more records.

Appreciate any help, please type in example instead of putting Qlikview files, thank you.

Re: Mode() Function for 1 record

tresB — Mon, 10 Jun 2019 08:09:50 GMT

It should not. If more that one value equally occur in the data scope, NULL is returned. For single record, it should return the value itself. Could you share a sample app that reflects the issue?

Re: Mode() Function for 1 record

lironbaram — Mon, 10 Jun 2019 08:15:32 GMT

the problem is not with, the mode function it works for 1 record , it's with your set expression

if you'll remove the set expression and manually select the current month you'll see you get a result

Re: Mode() Function for 1 record

cloud — Mon, 10 Jun 2019 08:41:44 GMT

Hi, I can't upload anything due to security issues, here is a sample example:

Name	Type	Location	Months	Reporting
Rosie	A	YYXX	3	May-19
John	A	XXSS	2	Apr-19
Rosie	B	AAWW	1	Apr-19
Sally	C	FFDD	2	Apr-19

So my current month in this sample is May, and I want to show "Rosie" in the text box as it is the only record for Mode, using my expression that I've placed above. It's a must to show only the current month. I've used Mode ()with Type with the same expression and for some reason it works fine. Not sure what's wrong.

Re: Mode() Function for 1 record

jonathandienst — Mon, 10 Jun 2019 08:28:18 GMT

>>Would like it to display the name regardless of whether it has 1 or more records.

Mode is a statistical function, not a text function. What output do you require?

>>This is my expression that displays the name in a textbox in Qlikview:

Not as far as I can see. Mode will return a number (or a NULL if the input is a text field).

Re: Mode() Function for 1 record

cloud — Mon, 10 Jun 2019 08:25:22 GMT

I need a text output, and I think Mode() can provide text output as well?

Re: Mode() Function for 1 record

jonathandienst — Mon, 10 Jun 2019 08:28:57 GMT

No - it cannot (and does not). You will get a null.

Re: Mode() Function for 1 record

cloud — Mon, 10 Jun 2019 08:34:20 GMT

Link

Referring to this, mode can display text values.

Re: Mode() Function for 1 record

lironbaram — Mon, 10 Jun 2019 09:38:05 GMT

i does return text , i think the confusion is with mod function

in your data your problem is the reporting column is not numeric

attach is a model where i changed it to date

Re: Mode() Function for 1 record

cloud — Tue, 11 Jun 2019 07:59:52 GMT

Hi Liron,
Like i've stated at my post, please type example as I can't open any Qlikview files. Thanks.

Re: Mode() Function for 1 record

lironbaram — Tue, 11 Jun 2019 09:17:57 GMT

there is nothing special in the expression

this is the expression :

=mode({<Reporting={"$(vCurrentMonth)"}>}name)

and this the variable expression

=date(max(Reporting),'MM/DD/YYYY')

the only other change is that the field Reporting

contains values like this 01/05/2019 for May and 01/04/2019 for April