Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anton1
Contributor
Contributor

Using aggregation in if clause

Hello,

I have a table with columns:

Date, Sales

I need to create a chart with Month as dimension, and Sum of sales for last sale day of the month in data as measure.

The last sales day of the month does not always equal to last day of the month, there might be dates with no sales.

For example:

Date DD.MM.YYYYSales
01.01.2019100
01.01.2019500
05.01.2019600
25.01.2019300
25.01.2019

400

And I should get:

MonthSum of last sale date sales
Jan 2019700

In February last sale date may be any date, and so on.

I need to calculate it in chart.

Thank you!

1 Reply
dplr-rn
Partner
Partner

easiest way would be to identify the last sales day per month while loading the data and introduce  a flag.

so in your example the rows for 25.01.2019  will have the flag there.

in the chart then you can use set analysis with flag.

e.g. Month year as dimension and Sum({<LastSalesDay={'Y'}>}Sales)