Solved: Re: Sum revenue for a specific date - Qlik Community

Anonymous · ‎2017-11-21

Hi, I have the following formula:

=AddMonths(Date(DATE#(CommonMonth,'YYYY/MM')),-1)

This returns the month previous to the selected date which is what I need, I now need to SUM(PrintRev) for the date this produces but I am confused about how to nest the SUM function,

Thanks

Anonymous · ‎2017-11-22

Ah I understand, in the formula I am converting 2017/10 to be something like 01/10/2017. This means when I go to look up my new date value in my CommonMonth column there is no such value as 01/10/2017, so I need to convert my date back to the original YYYY/MM format, and it should then find the correct values.

=Sum({<CommonMonth = {"$(=date(AddMonths(Date(DATE#(CommonMonth,'YYYY/MM')),-1),'YYYY/MM'))"}>} PrintRev)

I can confirm this has worked correctly for me. Thank you for your help.

View solution in original post

Anil_Babu_Samineni · ‎2017-11-21

Perhaps this?

Sum({<CommonMonth = {"$(=AddMonths(Date(DATE#(CommonMonth,'YYYY/MM')),-1))"}>} PrintRev)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

its_anandrjs · ‎2017-11-21

What is the format of CommonMonth field.

Anonymous · ‎2017-11-21

Format is YYYY/MM, when I run this formula it translates it to be: DD/MM/YYYY and converts to one month back which is the desire behaviour.

Anonymous · ‎2017-11-21

This returns 0.

its_anandrjs · ‎2017-11-21

Then just try this

SUM( {<CommonMonth = {"$(=AddMonths(GetFieldSelections(Order_Date),-1))"}>} PrintRev)

Let me know about this.

Anonymous · ‎2017-11-21

This returns 0.

its_anandrjs · ‎2017-11-21

Oh My bad maybe this is

SUM( {<CommonMonth = {"$(=AddMonths(GetFieldSelections(CommonMonth ),-1))"}>} PrintRev)

Better if you can share sample date for this.

Anil_Babu_Samineni · ‎2017-11-21

You can re write this

Sum({<CommonMonth = {"$(=AddMonths(Date(DATE#(CommonMonth,'YYYY/MM'), 'YYYY/MM'),-1))"}>} PrintRev)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anil_Babu_Samineni · ‎2017-11-21

Or this

Sum({<CommonMonth = {"$(=AddMonths(Date(DATE#(max(CommonMonth),'YYYY/MM'), 'YYYY/MM'),-1))"}>} PrintRev)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful