Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem with firstsortedvalue and set

Hi Geniuses,

I want to find out that in which month of the current year the lowest invoice processed excluding the current month

I am using the below formula to calculate the number of invoice processed

=min(aggr(num((count(distinct{<YEAR={$(=vYearToday)},MONTH-={$(=vMonthToday)}>} INVOICE_NUMBER)),'##0'),MONTH))

This is working fine and give me the correct result which is 1952

Now I want in which month this 1952 is coming from for that I am trying to use the below formula

=FirstSortedValue(MONTH,(aggr(num((count(distinct{<YEAR={$(=vYearToday)},MONTH-={$(=vMonthToday)}>} INVOICE_NUMBER)),'##0'),MONTH)))

In this formula as soon as I use the MONTH in the set analysis it is not returning me anything(highlighted in bold), why I am using the MONTH with -= as I want to exclude the current month, please help me out to solve this

vYearToday =Year(today())

vMonthToday  =Month(Today())

Thanks in advance,

S

9 Replies
shraddha_g
Partner - Master III
Partner - Master III

You can try this:

FirstSortedValue(MONTH,(aggr(num((count(distinct{<YEAR={$(=vYearToday)},MONTH={"<$(=vMonthToday)"}>} INVOICE_NUMBER)),'##0'),MONTH)))

Anonymous
Not applicable
Author

It is not working

shraddha_g
Partner - Master III
Partner - Master III

Please check what is variable is returning.

Format returned by Variable and that of Values from Month Field Should match.

also possible share screen shot of what output you are getting after evaluating the variable and values of Filter Month

Anil_Babu_Samineni

Can you post sample?

Please let me know whether this is returning anything for you?

=FirstSortedValue(MONTH, Aggr(count(distinct INVOICE_NUMBER),MONTH))


Then use this?

=FirstSortedValue(MONTH, Aggr(count({<YEAR={$(=vYearToday)},MONTH={"<$($(=vMonthToday))"}>} distinct INVOICE_NUMBER),MONTH))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

attached is the screenshot

error.JPG

I am getting no value after putting the formula you gave(left text box shows same), Month is coming as Jan Feb Mar and the vMonthToday variable is returning Sep shown in the right text box

Anonymous
Not applicable
Author

I think the problem is with the first sorted value

Anonymous
Not applicable
Author

Yes Anil the first expression returning me Sep,

I have tried the expression you gave but it is not returning anything

I think the problem is with the firstsortedvalue

Anil_Babu_Samineni

AFAIK, If first one returns some thing then how come FirstSortedValue is wrong. Could be problem with variables? You can work on variables or share sample to test

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Anil I did some analysis and found out that I have two month in which the invoices are the lowest and are exact same as 1952 so the firstsorted value is getting confused to sort it, please tell me now how to tackle such situation