topic Re: NonNull or Not Zero Values in QlikView

NonNull or Not Zero Values

sangeeth8787 — Thu, 30 May 2019 11:06:53 GMT

Hi,

I am using FirstSorted Value with Date to extract Max Date value of any choosen month

Expression: FirstsortedValue(SalesAmount,-TxnDt)

For example

Date SalesAmount

31/03/2019 -

30/03/2019 0

29/03/2019 150.

When the user selects Month March, The pivot chart Displays as

Current output

Month Value

2019-03 -

Expected Output

Month Value

2019-03 150 (Latest Value which is not Zero neither Null).

I am sure there would be some easy way to deal with this instead of choosing lengthy scripting like following.

if (FirstsortedValue(SalesAmount,-TxnDt) is (Null or Zero) ,

if(FirstsortedValue(SalesAmount,-TxnDt-1) is (Null or Zero),

if(FirstsortedValue(SalesAmount,-TxnDt-30) is (Null or Zero), (FirstsortedValue(SalesAmount,-TxnDt) ))))))))

Many Thanks

Re: NonNull or Not Zero Values

jaibau1993 — Thu, 30 May 2019 11:43:57 GMT

Hi! You can use set analysis inside FirstSortedValue so try something like the following:

FirstsortedValue({<SalesAmount ={"=len(Trim(SalesAmount))>0"}>} SalesAmount,-TxnDt)

Regards,

Jaime.

Re: NonNull or Not Zero Values

Anil_Babu_Samineni — Thu, 30 May 2019 12:17:47 GMT

How about this way?

Dimension as MonthName(Date)

and expression as

Sum({<Date={$(=Max(Date))}>} SalesAmount)

Re: NonNull or Not Zero Values

sangeeth8787 — Thu, 30 May 2019 13:09:34 GMT

Hi Anil,

Please correct me If I am wrong here, The QV executes the set analysis in the following order
Sum({<Date={$(=Max(Date))}>} SalesAmount)

Step 1)Date={$(=Max(Date))} = 31/03/2019,
Step2)Sum(on 31st March, Sales Amount Number) => Sum(-) = > -.

I have tried this expression in my qvw file, but it didn't worked here.

Re: NonNull or Not Zero Values

sangeeth8787 — Thu, 30 May 2019 13:25:16 GMT

Hi Jaibau,

I am glad for your message, The set expression you have provided earlier resolved my issue to get Latest Sales Amount, but if I want to get next non zero value? (Previous Sales Amount).

PreviousSalesAmount = firstsortedValue(SalesAmount,-Date,2) //If there are no non zero's null values, this expression would resolve the issue, but unfortunately this column has zero and Null Values.

Date SalesAmount

31/03/2019 -

30/03/2019 0

29/03/2019 150.

27/03/2019 0

26/03/2019 100.

Pivot Chart Output

Month LatestSalesAmount Previous SalesAmount SalesAmountMovement

March 2019 150 ?? LatestSalesAmount-PreviousSAmt.

Once again I am glad for you time.

Thanks

Re: NonNull or Not Zero Values

jaibau1993 — Fri, 31 May 2019 06:37:21 GMT

Hi!

If you want to avoid zero values of amount too (in addition to Null values) then use the following expression:

FirstsortedValue({<Amount ={"=len(Trim(Amount))>0 and Amount > 0"}>} Amount,-Date, 2)

In the set expression I am writting the condition that must meet the values of Amount in the subset taken for the calculation. Thus, the only thing was to add the condition "Amount > 0".

Regards,

Jaime.

Re: NonNull or Not Zero Values

sangeeth8787 — Fri, 31 May 2019 08:48:04 GMT

Hi Jaibau,

It worked for me. I really appreciate it.

Thanks

Sangeeth