Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
qvhjenvo
Contributor
Contributor

Get sum(sales) from previous period

Hi all,

I have selectors one for year and one for month.

I have an expression that calculates sum on "recovery_bad_debt_voucher_amount" based on selected date (year and month).

what I want is following:

The calculation is to get sum for "recovery_bad_debt_voucher_amount" for previous period. Dimension for the previous period is: prev_tert_date

sum({$<recovery_current_status_on_period_end_flag = {"-1"},

monthyear = {"$(=date(max(date#(prev_tert_date, 'YYYYMMDD')),'YYYYMM'))"}

>}recovery_bad_debt_voucher_amount)

The problem: this calculation does not give result for the previous period.

Somebody please help to figure out what wrong it is with monthyear-statement.

Regards

Loi

4 Replies
sunny_talwar

May be this?

='Previous bad_debt_amount  = ' & sum({$<recovery_current_status_on_period_end_flag = {"-1"},

ledger_balance_date = {"$(='>='& Date(MonthStart(Max(Date#(prev_tert_date, 'YYYYMMDD'))),'YYYYMMDD') & '<=' & Date(MonthEnd(Max(Date#(prev_tert_date, 'YYYYMMDD'))),'YYYYMMDD'))"}, monthyear

>}recovery_bad_debt_voucher_amount)


Capture.PNG

Greg_Williams
Former Employee
Former Employee

Perhaps change {"-1"} to {-1}

qvhjenvo
Contributor
Contributor
Author

Hi,

That works perfectly! Thanks a lot.

But I can not understand what it is different between my monthyear and yours.

Could u plizz give me some explanations

sunny_talwar

I used the date field instead of the monthyear field you were looking at. The reason I did that was because you were picking a date and were equating it to another date which did not match. I created a range from monthstart to monthend and that is why it worked.

Please close the thread if you got what you were looking for.

Best,

Sunny