Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Search instead for
Did you mean:
Not applicable

## To find the sum of a field for last day of month

Hi,

I need to find the sum of a field for last day of month

I have the data in  foll format

Date                                Pdt            SalesCount

2012-01-01   A                         100

2012-01-01   B                         100

2012-01-02   A       100

2012-01-02   B                         200

..

..

2012-01-31   A      300

2012-01-31   B      400

2012-02-01   A      200

2012-02-01   B      300

2012-02-02   A      400

2012-02-02   B      500

For each month, i need the sum of the SalesCount for last day of the month. In the above example, for Jan, i need the sum of sales count for 31st Jan and for Feb, i need the sum of salescount for 2nd Feb

I have Month as a dimension

Expected output:

2012 Jan  = 700

2012 Feb  = 900

Please suggest..

9 Replies
Luminary Alumni

Hi,

You can easily do this by adding a additional column in your script

LOAD

Date,

If(Date = MonthEnd(Date), 1, 0) AS IsMonthEnd,

'

'

'

FROM DataSource;

Now by the month end has 1 in IsMonthEnd Field.

In expression now you get the values as

=Sum({<IsMonthEnd={1}>} SalesCount)

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

Hi.

MonthEnd(Date) gives me last day of the month say for Feb 2012 it gives 29-Feb-2012, but say i have data only till 10th of Feb then i need the sum of salescount for 10th of Feb.

How to achieve this?

Not applicable
Author

Using month as a dimension try this:

sum(if(Date=max(Date),SalesCount))

MVP

Try advanced aggregation:

=sum(aggr(if(Date=max(total<Pdt,Month> Date),SalesCount),Pdt,Month,Date))

Luminary Alumni

Hi,

The expression suggested by Swuehl works perfectly for you scenario.  I think the Date should be removed at the end of the expression.  The modified expression would be

=sum(aggr(if(Date=max(total<Pdt,Month> Date),SalesCount),Pdt,Month))

Regards,

Jagan.

Not applicable
Author

Thank you guys, it helped me a lot to calculate stock level, instead of using firstsortedvalue().

I have one more question: is it possible to integrate set analysis using this expression?

I am using a Master Calendar and would like for i.e. to sum the last 12 months to make average

Thanks,

Quentin

Creator II

How to show  the last day sales in the pivot table ?

My current expression is ,sum(D.BILL_AMOUNT)/10000000.

and date field is D.Voucher

Contributor III

Hello,

did you find a solution

I have the same case but at the end of each week ?

Contributor II

hey it is possible by adding the additional script provided below:

LOAD

Date,

If(Date = MonthEnd(Date), 1, 0) AS IsMonthEnd,

'

'

'

FROM DataSource;

Now by the month end has 1 in IsMonthEnd Field.

In expression now you get the values as

=Sum({<IsMonthEnd={1}>} SalesCount)

Hope this helps you.

Regards,

Net Worth Updates

Community Browser