Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sarahshong
Contributor III
Contributor III

top of an aggregation in straight table

I have a straight table with multiple dimensions. I want to grab the top value (first of the month) for each product and month. I have an expression that has the price per day for a product. 

 

I want to see if I can aggregate  the expression top(TOTAL column(1)) by product and month. The result would be for each daily day, it would show the first price for the month for that product.

 

 

Any suggestions?

 

 

 

 

1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

try this:

in your table, add MONTH field which is monthstart(DATE)
in your chart, dimensions:
          PROD, 
          =if(date(aggr(nodistinct min(DATE),PROD, MONTH)) = DATE, DATE)  //this is calculated dim; set this as SUPPRESS WHEN VALUE IS NULL
in your expression :
          =sum(AMOUNT)  //your measure

date(aggr(nodistinct min(DATE),PROD, MONTH)) is the first of the month for each prod/month combination
when this is equal to DATE, show DATE, else NULL and the row will be suppressed

View solution in original post

2 Replies
sunny_talwar

@sarahshong not entirely sure I understand what you are looking to do. Is there a sample you can share and the expected output based on the sample provided?

edwin
Specialist III
Specialist III

try this:

in your table, add MONTH field which is monthstart(DATE)
in your chart, dimensions:
          PROD, 
          =if(date(aggr(nodistinct min(DATE),PROD, MONTH)) = DATE, DATE)  //this is calculated dim; set this as SUPPRESS WHEN VALUE IS NULL
in your expression :
          =sum(AMOUNT)  //your measure

date(aggr(nodistinct min(DATE),PROD, MONTH)) is the first of the month for each prod/month combination
when this is equal to DATE, show DATE, else NULL and the row will be suppressed

View solution in original post