Mode() Function for 1 record

[cloud]

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.

[tresesco]

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?

[lironbaram]

HI 

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 

[cloud]

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.

[jonathandienst]

>>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).

[cloud]

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

[jonathandienst]

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

[cloud]

Link

Referring to this, mode can display text values. 

[lironbaram]

hi 

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 

[cloud]

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