Qlik Community

Ask a Question

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 
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
MVP
MVP

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
MVP
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

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

RaniaMH
Contributor
Contributor

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