Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

NonNull or Not Zero Values

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

2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

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

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

 

Regards,

Jaime.

View solution in original post

Highlighted
Partner
Partner

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.

View solution in original post

6 Replies
Highlighted
Partner
Partner

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

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

 

Regards,

Jaime.

View solution in original post

Highlighted

How about this way?

Dimension as MonthName(Date)

and expression as

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

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

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

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

Highlighted
Partner
Partner

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.

View solution in original post

Highlighted
Contributor III
Contributor III

Hi Jaibau,

 

It worked for me. I really appreciate it.

 

Thanks

Sangeeth