topic Re: Problem with firstsortedvalue and set in QlikView

Problem with firstsortedvalue and set

Anonymous — Mon, 11 Sep 2017 05:15:17 GMT

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,

Re: Problem with firstsortedvalue and set

shraddha_g — Mon, 11 Sep 2017 05:27:29 GMT

You can try this:

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

Re: Problem with firstsortedvalue and set

Anonymous — Mon, 11 Sep 2017 06:05:53 GMT

It is not working

Re: Problem with firstsortedvalue and set

shraddha_g — Mon, 11 Sep 2017 06:14:51 GMT

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

Re: Problem with firstsortedvalue and set

Anil_Babu_Samineni — Mon, 11 Sep 2017 06:19:24 GMT

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

Re: Problem with firstsortedvalue and set

Anonymous — Mon, 11 Sep 2017 06:23:48 GMT

attached is the screenshot

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

Re: Problem with firstsortedvalue and set

Anonymous — Mon, 11 Sep 2017 06:24:29 GMT

I think the problem is with the first sorted value

Re: Problem with firstsortedvalue and set

Anonymous — Mon, 11 Sep 2017 06:26:02 GMT

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

Re: Problem with firstsortedvalue and set

Anil_Babu_Samineni — Mon, 11 Sep 2017 06:27:18 GMT

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

Re: Problem with firstsortedvalue and set

Anonymous — Mon, 11 Sep 2017 07:01:39 GMT

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