12 Replies Latest reply: Feb 11, 2018 4:06 AM by Arvind Kumar Jha

# Dynamic Measure based on Dates

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

• ###### Re: Dynamic Measure based on Dates

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

• ###### Re: Dynamic Measure based on Dates

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.

• ###### Re: Dynamic Measure based on Dates

try this,

vFrom = Date(Max(DateField))

vStart = Date(GetFieldSelections(DateField))

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

• ###### Re: Dynamic Measure based on Dates

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?

• ###### Re: Dynamic Measure based on Dates

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

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:

Hope that was clear?

• ###### Re: Dynamic Measure based on Dates

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

• ###### Re: Dynamic Measure based on Dates

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)

• ###### Re: Dynamic Measure based on Dates

Hello Omar,

Thanks a lot for the reply.

The above worked ,but the same through YearMonth field doesn't work like below :

sum({<Year=,Month=,[Launch Date]={">\$(=Date(min([[Launch Date.autoCalendar.YearMonth]])))

<\$(=Date(max([[Launch Date.autoCalendar.YearMonth]])))"}>}Sales)

Actually i wanted filter to be as YearMonth

Thanks,

Arvind

• ###### Re: Dynamic Measure based on Dates

Hi Arvind,

When facing trouble with these expressions; always create text object;

and create a measure containing the parts you're having a doubt about;

I mean : create these measures:

=Date(min([[Launch Date.autoCalendar.YearMonth]]))

=Date(max([[Launch Date.autoCalendar.YearMonth]]))

and see what they're returning; and from there, begin to alter them untill it corresponds to ur need and they return the expected dates in the format of ur Launch Date

• ###### Re: Dynamic Measure based on Dates

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

• ###### Re: Dynamic Measure based on Dates

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.

• ###### Re: Dynamic Measure based on Dates

Hello Omar,

Actually i had already created these measures and they are returning correct values but not giving correct results :

sum({<Year=,Month=,[Launch Date]={">\$(=Date(min([[Launch Date.autoCalendar.YearMonth]])))

<\$(=Date(max([[Launch Date.autoCalendar.YearMonth]])))"}>}Sales)