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

[Sellls]

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!!

[paulselousyoriz]

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

[LReeve]

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!

[Sue_Macaluso]

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

[Sellls]

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!

[Sellls]

Thanks @LReeve!

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.

[Sellls]

Hi Sue @Sue_Macaluso 

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

[LReeve · Accepted solution]

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