Dynamic Measure based on Dates

[arvindjha2050]

Hello Team,

I have a Filter and a Table.

Based on my selection of date in filter i want to know the sales on that particular day and also incremental sales after the selected date in a table

To achieve the same i used greater than format but it doesn't work.

Sum({$<[Last Update Date] = {"> $(=getfieldselections([Last Update Date]))"}>}[Sales])

Kindly help

Thanks,

Arvind

[kaushiknsolanki]

Try this.

Sum({$<[Last Update Date] = {">= $(=Max([Last Update Date]))"}>}[Sales])

If you have selected the year and month fields also then you should remove them as well, like shown below.

Sum({$<[Last Update Date] = {">= $(=Max([Last Update Date]))"},Month=,Year=>}[Sales])

Regards,

Kaushik Solanki

[agigliotti]

your expression looks correct and should give you what you are looking for.

if you make selections also on month or year field then follow what kaushik.solanki‌ said.

[ChennaiahNallani]

try this,

vFrom = Date(Max(DateField))

vStart = Date(GetFieldSelections(DateField))

Sum({< Date = {'>=$(=Date(vStart))<=$(=Date(vFrom))'} >} Sales)

[arvindjha2050]

Hello Kaushik,

I dint get your second expression :

Sum({$<[Last Update Date] = {">= $(=Max([Last Update Date]))"},Month=,Year=>}[Sales])

How is it useful?

Can it be explained with example?

[arvindjha2050]

Hello Nallani,

Can we get sum between two dates which is not maximum or minimum and can be any random value?

For example Dates range from Jan 2010 to Jan 2018 ?

If i want to see sales between Mar 2013 to Jan 2014 based on filter section, i.e. basically there will be two filters,From date and To date for the same date field

[agigliotti]

you can create a new field in your script as below:

Year(your_date_field) & '-' & Month(your_date_field)

and use that field to make time period selection as you want.

[OmarBenSalem]

max([Last Update Date]) will return the max of what Qlik is "seing"; I mean when u select nothing, Qlik is seing everything and thus will return u the max.

Now Imagine that u select : [Last Update Date] = 05/01/2018 ; now u're forcing qlik to only "see" this values;

so the max([Last Update Date]) = max('05/01/2018') = '05/01/2018'

Now u want to see the sum of ur measure for the date u're selecting and all the dates that come after it.

as we said the max([Last Update Date]) returns the selected date.

so to return the >= selected date we need to add >=

Now, in ur set expression it would be sthing like this:

sum({< [Last Update Date] ={">=$(=max([Last Update Date]))"}>}Sales)

if u for for example, want, if u select '05/01/2018' want to see sum Sales from the 05/01/2017 (one year prior) to 05/02/2018 (add 1 month)

to return one year: AddYears(max([Last Update Date]),-1) => 05/01/2017

to add one Month : AddMonths(max([Last Update Date]),1) =>05/02/2018

BUT: if we select 05/01/2018 => The selected date is within the YEAR 2018, so normally, Qlik will only see data relative to YEAR =2018; so to break this, and make Qlik always SEE all the YEARS (to be able to return to the prior one and fetch the sales within it, we use the Key word : Year= Which the same as saying Year={*}

Which means, Hey QLIK, I want u to keep seing ALL THE YEARS, even though I've selected a Year, or a date within ONE year.. (Same logic for the Month field)

so the expression would become:

sum({<Year=,Month=, [Last Update Date] ={">=$(=AddYears(max([Last Update Date]),-1)) <=$(=AddMonths(max([Last Update Date]),1))"}>}Sales)

Hope that was clear?

[arvindjha2050]

Hello Omar,

Thanks for the elaborate explanation.

But is there a way to get sum of a measure between random dates selected by a user?

Thanks,

Arvind

[OmarBenSalem]

u'd have a date field; the user select 2 dates from the same field; and u want to return the sales btwn the 2 selected dates?

so If ur user select, 15/11/2017 and 11/01/2018 , u want sales btwn those 2 dates right?

now, if I have well explained the process to u, u should figure it out by urself

if u select these 2 dates:

to retrieve the min date btwn the both? =>15/11/2017 => min(Date)

to retrieve the max date btwn the both? =>11/01/2018 => max(Date)

=> sum({<Year=,Month=,Date={">=$(=min(Date)) <=$(=max(Date))"}>}Sales)