Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Using set analysis to analyze data only in the latest report date

Hi,

I'm working on a database of reports that get generated every day. Each day, for one of the charts, I would like to see the analyzed data only for the latest report date. 

For example. in a particular table, I would like to see the sum of EstSales according to various groups of products, but only for the latest report date.

I've used the following formula but it gives me an error.

Sum({$<[Report Date]={max([Report Date])}>} EstSales)

 

If I substitute max([Report Date]) with the actual date '21/01/2020', using the following code below, the formula works fine. But this would entail me changing the date in the formula manually daily.

Sum({$<[Report Date]={'20/1/2020'}>} EstSales)

Interestingly, the formula max([Report Date]) works fine when I'm trying to display this in the title of the chart. But somehow does not work in the expression i'm using for the calculation

Eg. ='Jobs by Part Number as of ' & max([Report Date]) shows up as "Jobs by Part Number as of 21/1/2020"

Is there something wrong with my set analysis formula or do I need to use a different date formula to achieve the correct result?

 

Thanks very much!!

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Hi @Sellls ,

You should only be replacing the first dollar.

 Sum({1<[Report Date]={'$(=max([Report Date]))'}>} EstSales)

That should lock your max report date. Now if you need to be able to have the value react to other selections but not Report Date you can create a variable (in data load or in app with a 1 in the set expression)  which you can reference instead of Max(Report Date).

Hope that helps

View solution in original post

7 Replies
Highlighted
Partner
Partner

Hi Sells,

ReportDate is a string, so you need to put speech/quotation marks around it to make the set analysis work, and add an equals sing before the function.

So the amended Set analysis formula should be:

 

Sum({$<[Report Date]={'=max([Report Date])'}>} EstSales)

 

If this solves your problem, then please mark this posting as Correct.

 

Paul

Highlighted
Contributor III
Contributor III

If using a function would you not need the Dollar expansion?

 

So something like: Sum({$<[Report Date]={'$(=max([Report Date]))'}>} EstSales)

 

Without I think it evaluates as =max([Report Date]) rather than the date itself!

Highlighted
Community Manager
Community Manager

@Sellls Are you using QlikView or Qlik Sense?  I would like to move this into the correct product forum. 

Sue Macaluso
Highlighted
Contributor III
Contributor III

Hi Paul,

Unfortunately that doesnt work 😞 There is no error in the expression but it returns just the entire sum of all the sales regardless, so its like not having any set analysis specified.

 

Thanks for the suggestion though!

Highlighted
Contributor III
Contributor III

Thanks @lukereeve89!

Using the $ expansion helped to a gd extent! It will take the latest report date in the selection.

Which means it works fine when no selection is made. But if I select 20/1/2020 and 19/1/2020, it would give me the sum of Est Sales in 20/1/2020, even though I just want the sum of Est Sales in the latest report date in the entire dataset, which would be 21/1/2020.

Wonder if there is a way to fix in to the latest report date regardless of selection? I tried to replace the $ with a 1 instead, but it just gives me $0 for everything.

Highlighted
Contributor III
Contributor III

Hi Sue @Sue_Macaluso 

I'm using Qlik Sense -did I start the discussion in the wrong forum? Sorry!

Highlighted
Contributor III
Contributor III

Hi @Sellls ,

You should only be replacing the first dollar.

 Sum({1<[Report Date]={'$(=max([Report Date]))'}>} EstSales)

That should lock your max report date. Now if you need to be able to have the value react to other selections but not Report Date you can create a variable (in data load or in app with a 1 in the set expression)  which you can reference instead of Max(Report Date).

Hope that helps

View solution in original post