NonNull or Not Zero Values

[sangeeth8787]

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

[jaibau1993 · Accepted solution]

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

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

Regards,

Jaime.

[Anil_Babu_Samineni]

How about this way?

Dimension as MonthName(Date)

and expression as

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

[sangeeth8787]

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.

[sangeeth8787]

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.

Eg 

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

[jaibau1993 · Accepted solution]

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.

[sangeeth8787]

Hi Jaibau,

It worked for me. I really appreciate it.

Thanks

Sangeeth