Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wlabarca99
New Contributor

SUM on the 2nd to last data point

Hello Everyone,

I am having some difficulties with a SUM expression in which I am calculating overstock based on the date of the data pull.  I need Last Weeks overstock but the days of loads are not always a perfect 7 days.

What I need is the second to last data point in this chart:

Capture.JPG

Currently my formula looks like this:

SUM({$<[inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date]={"$(=DATE(Today()-7))"}>}

IF(([inv_age_rpt.qt_91_180]+[inv_age_rpt.qt_181_270]+[inv_age_rpt.qt_271_365]+[inv_age_rpt.qt_365_plus]-[inv_age_rpt.sls_past_60])*prdct_cst>0,

(([inv_age_rpt.qt_91_180]+[inv_age_rpt.qt_181_270]+[inv_age_rpt.qt_271_365]+[inv_age_rpt.qt_365_plus]-[inv_age_rpt.sls_past_60])*prdct_cst),0))

If today is 4/23, 7 days ago (4/16) is not a data point plotted on this chart, so I need to go back to the NEXT date which would be 4/13.

Any assistance would be very helpful thank you!!

7 Replies
MVP
MVP

Re: SUM on the 2nd to last data point

May be like this

FirstSortedValue({$<[inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date]={"$(='<=' & DATE(Today()-7))"}>} Aggr(Sum(RangeMax(([inv_age_rpt.qt_91_180]+[inv_age_rpt.qt_181_270]+[inv_age_rpt.qt_271_365]+[inv_age_rpt.qt_365_plus]-[inv_age_rpt.sls_past_60])*prdct_cst, 0)), -[inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date])

wlabarca99
New Contributor

Re: SUM on the 2nd to last data point

Sunny,

Not the solution, this came back null.

MVP
MVP

Re: SUM on the 2nd to last data point

My bad, Try this

FirstSortedValue({$<[inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date]={"$(='<=' & DATE(Today()-7))"}>} Aggr(

Sum(RangeMax(([inv_age_rpt.qt_91_180]+[inv_age_rpt.qt_181_270]+[inv_age_rpt.qt_271_365]+[inv_age_rpt.qt_365_plus]-[inv_age_rpt.sls_past_60])*prdct_cst, 0))

, [inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date])


, -[inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date])

wlabarca99
New Contributor

Re: SUM on the 2nd to last data point

Null again

MVP
MVP

Re: SUM on the 2nd to last data point

Would you be able to share a sample to look at this?

wlabarca99
New Contributor

Re: SUM on the 2nd to last data point

I figured out a workaround by just using a few IF statements

MVP
MVP

Re: SUM on the 2nd to last data point

Super

Community Browser