# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results 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

9 Replies
MVP

Hi,

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

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

MVP

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

Hello,

did you find a solution

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

Contributor II

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,