Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

Tags (2)
9 Replies
Highlighted
Partner
Partner

Re: Problem with firstsortedvalue and set

You can try this:

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

Highlighted
Contributor III
Contributor III

Re: Problem with firstsortedvalue and set

It is not working

Highlighted
Partner
Partner

Re: Problem with firstsortedvalue and set

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

Highlighted

Re: Problem with firstsortedvalue and set

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

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)
Highlighted
Contributor III
Contributor III

Re: Problem with firstsortedvalue and set

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

Highlighted
Contributor III
Contributor III

Re: Problem with firstsortedvalue and set

I think the problem is with the first sorted value

Highlighted
Contributor III
Contributor III

Re: Problem with firstsortedvalue and set

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

Highlighted

Re: Problem with firstsortedvalue and set

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

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)
Highlighted
Contributor III
Contributor III

Re: Problem with firstsortedvalue and set

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