Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Minimum Date In Chart

The age old question - how can I get the minimum date to use in an expression in a graph with a date dimension. Using set analysis only gives you the minimum of the date for the entire selection, however I need the minimum date for the time dimension of the graph, i.e. the graph has a time dimension of month, so for every month's bar in the graph, the minimum date should be the first of the month.

I need to get the opening customer count and this is the wrong expression:

sum( {< Period_date = {$(=min(Period_date)} >} cust_count)

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Set analysis is calculated once per chart, you have use Aggr() like below

Sum(Aggr(If(Period_date = Min(TOTAL <Month> Period_date),  sum( cust_count)), Month, Period_date))


Note: Replace Month with your actual field name.


Hope this helps you.


Regards,

jagan.

View solution in original post

7 Replies
Anonymous
Not applicable
Author

try this?

=sum( {< Period_date = {'$(=Date(min(Period_date),'DD/MMM/YYYY'))'} >} cust_count)


Make sure Format of Period_date should be 'DD/MMM/YYYY'

Anonymous
Not applicable
Author

By using the peek function to get the min date and by using the set analysis to get the min date  vaules

MayilVahanan

Hi

If you are using Date dimension, then try like this

Create a variable like vMinPeriodDate = Min(Period_Date)

After that, use that variable in ur set analysis

=sum( {< Period_date = {'$(=Date($(vMinPeriodDate) ,'DD/MMM/YYYY'))'} >} cust_count)

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
luciancotea
Specialist
Specialist

Set analysis is calculated ONCE, per chart, not for every value.

Not applicable
Author

That's the problem. If set analysis was calculated for every value in the chart it would have worked.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Set analysis is calculated once per chart, you have use Aggr() like below

Sum(Aggr(If(Period_date = Min(TOTAL <Month> Period_date),  sum( cust_count)), Month, Period_date))


Note: Replace Month with your actual field name.


Hope this helps you.


Regards,

jagan.

Anonymous
Not applicable
Author

Jagan, You rock man. Great answer. Thanks.