Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Sum Value Based on Max/Min Period

I have built the straight table at the bottom of my message. What I would like to do, is get the MIN FISCAL PERIOD sum(Sales) per COMPANY and the MAX FISCAL PERIOD sum(Sales) per Company. This would allow me to see if companies are growing or not.

So final product would look some like:

CompanyInitialLatestGrowth
Adventure Works$500$1500300%
Ice Cream Store$100$30(70%)
Dog Store$300$30001000%
CompanyFiscal Periodsum(Sales)
$395,387
Adventure WorksOct 2010$500
Adventure WorksNov 2010$500
Adventure WorksDec 2010$500
Adventure WorksJan 2011$500
Adventure WorksFeb 2011$500
Adventure WorksMar 2011$500
Adventure WorksApr 2011$500
Adventure WorksMay 2011$500
Adventure WorksJun 2011$500
Adventure WorksJul 2011$500
Adventure WorksAug 2011$500
Adventure WorksSep 2011$1,000
Adventure WorksOct 2011$1,000
Adventure WorksNov 2011$1,000
Adventure WorksDec 2011$1,000
Adventure WorksJan 2012$1,500
Adventure WorksFeb 2012$1,500
Adventure WorksMar 2012$1,500
Adventure WorksApr 2012$1,500
Adventure WorksMay 2012$1,500
Adventure WorksJun 2012$1,500
Adventure WorksJul 2012$1,500
Adventure WorksAug 2012$1,500
Adventure WorksSep 2012$1,500
Adventure WorksOct 2012$1,500
Adventure WorksNov 2012$1,500
Adventure WorksDec 2012$1,500
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Sunny's solution worked for your sample records, right?

What is the difference to your real data? Can you describe your data a little more?

You can add the Sales value set expression filter to both FirstSortedValue({<...>} .. ) and also to the second solution (just add it to every aggregation function)

edit:

Modifying Sunny's QVW and expressions:

=FirstSortedValue({<Sales ={">0"} >} Sales, FiscalPeriod)

=FirstSortedValue({<Sales ={">0"} >} Sales, -FiscalPeriod)

and coming back to your approach:

= sum({< MonthDate= {'$(= min({< Sales = {"> 0 "}>} MonthDate))'}>}Sales)


Set Analysis and Dollar sign expansion is only evaluated once per chart, not per dimension line, so this will not determine the min MonthDate (with Sales >0) per Company, it will filter on the overall minimum MonthDate (and you may need to double check the format of the MonthDate modifier value:

Dates in Set Analysis

)

View solution in original post

6 Replies
Highlighted

Check out the attached

Capture.PNG

Highlighted
Creator III
Creator III

Not showing anything when I try to apply to my qvw. Not sure what I am missing...

Highlighted
Creator III
Creator III

So I've figured out how to get the earliest and latest dates per company. I have a straight table setup with Company as the only dimension and these two expressions:

= min({< Sales = {"> 0 "}>} MonthDate)

= max({< Sales = {"> 0 "}>} MonthDate)

These get me the earliest dates of sale for each company but I would like the actual Sale VALUE that corresponds with these dates. Any suggestions?

Highlighted
MVP
MVP

Maybe you have multiple records for each company per month?

Try for example

=FirstSortedValue(Aggr(Sum(Sales), Company,FiscalPeriod),Aggr(FiscalPeriod,Company,FiscalPeriod))

=FirstSortedValue(Aggr(Sum(Sales), Company,FiscalPeriod),-Aggr(FiscalPeriod,Company,FiscalPeriod))

or

=Sum(Aggr( If(FiscalPeriod = Min(Total<Company> FiscalPeriod),Sales), Company,FiscalPeriod))

=Sum(Aggr( If(FiscalPeriod = Max(Total<Company> FiscalPeriod),Sales), Company,FiscalPeriod))

Highlighted
Creator III
Creator III

Thank you for your reply Stefan but none of those worked for me.I can guarantee there are not multiple records per company per month. Note that I have transitioned from FiscalPeriod to MonthDate

I have had success only with below to get the min or max date :

= min({< Sales = {"> 0 "}>} MonthDate)

I somehow have to work this into a set analysis expression, I took a crack at it with no luck

= sum({< MonthDate= {'$(= min({< Sales = {"> 0 "}>} MonthDate))'}>}Sales)

I feel like this is a pretty basic ask, so I dont know why I am struggling so much. You understand what I am trying to do - I just want the earliest Sales amount per company and the latest Sales amount so I can compare.

Highlighted
MVP
MVP

Sunny's solution worked for your sample records, right?

What is the difference to your real data? Can you describe your data a little more?

You can add the Sales value set expression filter to both FirstSortedValue({<...>} .. ) and also to the second solution (just add it to every aggregation function)

edit:

Modifying Sunny's QVW and expressions:

=FirstSortedValue({<Sales ={">0"} >} Sales, FiscalPeriod)

=FirstSortedValue({<Sales ={">0"} >} Sales, -FiscalPeriod)

and coming back to your approach:

= sum({< MonthDate= {'$(= min({< Sales = {"> 0 "}>} MonthDate))'}>}Sales)


Set Analysis and Dollar sign expansion is only evaluated once per chart, not per dimension line, so this will not determine the min MonthDate (with Sales >0) per Company, it will filter on the overall minimum MonthDate (and you may need to double check the format of the MonthDate modifier value:

Dates in Set Analysis

)

View solution in original post