Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

12 Replies
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Partner
Partner

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.

Creator III
Creator III

try this,

vFrom = Date(Max(DateField))

vStart = Date(GetFieldSelections(DateField))

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

Contributor III
Contributor III

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?

Contributor III
Contributor III

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

Partner
Partner

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.

Partner
Partner

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?

Contributor III
Contributor III

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

Partner
Partner

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)