Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
jagan
Luminary Alumni
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))

swuehl
MVP
MVP

Try advanced aggregation:

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

jagan
Luminary Alumni
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

venkat1
Creator II
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

RanMH
Contributor III
Contributor III

Hello,

did you find a solution 

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

johnson532
Contributor II
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