Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
francisco_olea
Contributor
Contributor

Calculate the average sales per month plus the actual days in the current month.

Im trying to calculate the average per month and just counting the current days in the actual month.

I have no problem calculated the output as long as I am selecting the current month. 

The problem with my formula is that when I try to filter other months without selecting the current month, the output of my formula will be incorrent. 

This is what im using:

Sum(SALES)/

(sum(TOTAL Aggr(COUNT(DISTINCT{ [YEARMONTH]), [YEARMONTH]))

-1+ (left(today([DATE]),2)/left(monthend(today([DATE])),2)))

 

Does anyone has any suggestions?

 

 

2 Replies
Carlos_Reyes
Partner
Partner

Mm, I don't think the Today() function can receive parameter values different than (0,1,2)
https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun...

Also, the denominator formula seems too complicated if you just want to count the number of distinct dates or days associated to the selected month... maybe "Count(Distinct [DATE])" would be a better choice for the denominator ?
francisco_olea
Contributor
Contributor
Author

The reason of the aggregate function is that I don't want to take into account the months that didn't have a sale.

 

so if today is 23/07 I want the division to be the = sum count of the selected months + 23/30 -1 (The minus one being the actual month).