Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
harleen_singh
Creator III
Creator III

Maximum date of every month

   Hello i have a data like this

Month yearDateQuantity
Dec 2012`01/12/20125
Dec 2012`15/12/201210
Jan 201301/01/201315
Jan 201315/01/201320
Feb 201301/02/201325
Feb 201315/02/201330



I am using only Month year dimension in Straight chart.

I want to see the sum of only maximum date of every month for example only date 15/12/2012 in December. Result shold look like following

Month yearSum
Dec 201210
Jan 201320
Feb 201330

  

What formula should i use??

thanks

lavi

1 Solution

Accepted Solutions
perumal_41
Partner - Specialist II
Partner - Specialist II

Hi Lavi,

Sum(if(Date=Aggr(Nodistinct Max(Date),MonthYear),Quantity,0)

Use this expression

Regards

Perumal A

View solution in original post

4 Replies
perumal_41
Partner - Specialist II
Partner - Specialist II

Hi Lavi,

Sum(if(Date=Aggr(Nodistinct Max(Date),MonthYear),Quantity,0)

Use this expression

Regards

Perumal A

somenathroy
Creator III
Creator III

sum(if(Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')=Aggr(Nodistinct Max(Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')),[Month year]),Quantity))

Regards,

Som

Not applicable

Hi lavi_musiclife,

                        In the script do the following:

 

T1:
Load * Inline [
Month_year,Date1,Quantity
Dec 2012,01/12/2012,5
Dec 2012,15/12/2012,10
Jan 2013,01/01/2013,15
Jan 2013,15/01/2013,20
Feb 2013,01/02/2013,25
Feb 2013,15/02/2013,30
];

left join(T1)
Load Distinct
Month_year,
date(max(Date1),'DD/MM/YYYY') as MaxDate
Resident T1
Group by Month_year;

Then, in the UI, for the straight table chart:

Dimension: Month_year

Expression: =sum({$<Date1=P(MaxDate)>} Quantity)

Hope this helps. Thanks.

nagaiank
Specialist III
Specialist III

The attached applucation willl be of help.