KPI while applying max date

[dia2021]

Hi All,

I'm trying to create a KPI by applying the sum to the particular fields in a column. I want Order Type = Clothes, shoes, pants to group and take a sum by most recent date. 

I applied this formula and it is working fine Sum({<Date={"$(=Max(Date))"}>}Order Type='Clothes') 

But I'm getting output in a negative (minus). I'm not sure why it is doing this. It is giving me -96 instead of 96.

Secondly, how can I combine another order type in the same statement??

Sum({<Date={"$(=Max(Date))"}>}Order Type='Clothes', shoes', 'pants')  This syntax is not working when I'm combining order type) 

[anthonyj · Accepted solution]

Hi @dia2021 ,

Your syntax is not quite right in your set analysis which may be causing the negative numbers. To combine values in your set analysis you only need to add them as a comma separated list between the curly braces. 

Try this:

Sum({<Date={"$(=Max(Date))", Order Type={'Clothes', shoes', 'pants'}>} [Column])

Regards

Anthony

[dia2021]

Thanks for the quick reply Anthony and your suggestion! That is helpful. I know now that the wrong syntax is the cause of the negative number.

The above syntax that you have suggested is throwing an error. Would you please help me in fixing the syntax? 

[anthonyj]

Sorry about that @dia2021 , I wrote that straight in without the Qlik editor and missed a curly brace after the max date and an apostrophe before 'shoes'.

Try this one.

Sum({<Date={"$(=Max(Date))"}, Order Type={'Clothes', 'shoes', 'pants'}>} [Column])

Thanks

Anthony

[dia2021]

I tried this one too but it is providing the output as 0. Could you please suggest what changes can I do to get the right numbers?

[anthonyj · Accepted solution]

The issue might be around the format of your Date column. If it has been read in as a date (ie date(Date) as Date) then your $(=Max(Date)) will come up with a value at the bottom of your editor that looks like a date (eg. 4/07/2020)

If it has not been read in as a date then $(=Max(Date)) will return the number of days from 01/01/1900 and will need to be formatted. Eg 44078 for 04/09/2020. The format will need to match the format that is in your Date field. So 4/9/2020 does not equal 04/09/2020. This I suspect is the issue with your data. The Date field has not been read in as a date.

So two choices if this is the issue:

Format Date in your load script and leave the set analysis as "$(=Max(Date))".

date(Date) as Date

Change the set analysis to format your Date field

"$(=date(Max(Date),'DD/MM/YYYY'))"

Let me know if this works.

Thanks

Anthony

[Vegar · Accepted solution]

The max() will always return the numeric value of a Dual() such as your Date. In set analysis you need the string value when comparing values a list of values.

I would go for Anthony's second suggestion to solve your issue.

Sum({<Date={"$(=Date(Max(Date),'DD/MM/YYYY'))"}, Order Type={'Clothes', 'shoes', 'pants'}>} [Column])

alternative is to use maxstring(). MaxString returns the string value.

Sum({<Date={'$(=MaxString(Date))'}, Order Type={'Clothes', 'shoes', 'pants'}>} [Column])

[dia2021]

Thank you both!! It worked 🙂

[dia2021]

I have another question - How can I combine multiple conditions in the same statement?

I mean like a "where" condition in SQL. It needs to satisfy both conditions of Order Type and Order Number in the set expression. I tried this expression which is not working.

Sum({<Date={'$(=MaxString(Date))'}, Order Type={'Clothes', 'shoes', 'pants'} & Order Number ={'12345'}>} [Column]) 

Please help me in fixing this syntax. 

[Vegar · Accepted solution]

Try

Sum({<Date={'$(=MaxString(Date))'}, Order Type={'Clothes', 'shoes', 'pants'}, Order Number ={'12345'}>} [Column])