Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Newb needs help - variables and dates in expressions

Quick information: InvDate and Qty come from an Excel spreadsheet which is loaded. Date is in MM/DD/YYYY format, quantity is an integer. RealDate comes from MasterCalendar script. A KPI with the expression of max(RealDate) or min(RealDate) display the correct dates.

I have created a KPI where I am trying to get the inventory for the start and end of the selected date range. When using Sum({$<[InvDate] = {'08/20/2017'}>}Qty) I get the correct information in the KPI.

What I really need, however, is the information to update based on the dates selected. So I tried Sum({$<[InvDate] = {"Date(max(RealDate),'MM/DD/YYYY')"}>}Qty) but I get 0.

I also tried Sum({$<[InvDate] = {"Date($(vMaxRealDate),'MM/DD/YYYY')"}>}Qty) where vMaxRealDate is a variable of Max(RealDate). This also gets me a result of 0.

Please help, as this is the big thing holding me back from finishing an important project for the company.

Thanks in advance.

1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

Re: Newb needs help - variables and dates in expressions

maybe this:

Sum( {$ < [InvDate] = {"$(=Max(Date(RealDate,'MM/DD/YYYY')))"} >} Qty )

3 Replies
agigliotti
Honored Contributor II

Re: Newb needs help - variables and dates in expressions

maybe this:

Sum( {$ < [InvDate] = {"$(=Max(Date(RealDate,'MM/DD/YYYY')))"} >} Qty )

Not applicable

Re: Newb needs help - variables and dates in expressions

Thank you very much. I have been trying to figure this out for almost 2 business days.

MVP
MVP

Re: Newb needs help - variables and dates in expressions

Try this

Sum({$<[InvDate] = {"$(=Date(Max(RealDate),'MM/DD/YYYY'))"}>} Qty)