topic Re: Where function is not working as expected in App Development

Where function is not working as expected

JohannesBoehmer — Thu, 10 Nov 2022 09:55:04 GMT

Hi all,

I´ve go following table:

F_ALL:
LOAD
PRCTR as ProfitCenter_WB,
CustomerName as CustomerName_WB,
KUNNR as CustomerNumber_WB,
OverdueDays as OverdueDays_WB,
SnapshotDate as SnapshotDate_WB,
BETRAG_EUR as BETRAG_EUR_WB,
DUE_0+DUE_30+DUE_60+DUE_90 as Overdue_WB,
DUE_90 as DUE_90_WB,
DUE_60 as DUE_60_WB,
DUE_30 as DUE_30_WB,
DUE_0 as DUE_0_WB,
ApplyMap('Map_DS',%KeyDS) as Segment_WB,
ApplyMap('Map_Country',%KeyARE) as Country_WB,
ApplyMap('Map_Region',%KeyARE) as Region_WB,
ApplyMap('Map_ARE',%KeyARE) as ARE_WB
FROM [$(vFolderFact)F_AM_DASH_COMPLETE_2022_SMO050.qvd]
(qvd)
Where DUE_90>=200000 or DUE_60>=500000 and SnapshotDate=date(date#(monthend(now(),-1),'YYYY-MM-DD'),'YYYY-MM-DD');

My problem now is, I want to select only the SnapshotDate which is the last day of last month and additionally where DUE_90 is bigger then 200.000 or DUE_60 is bigger then 500.00.

When I only use Where SnapshotDate=date(date#(monthend(now(),-1),'YYYY-MM-DD'),'YYYY-MM-DD'); it selects 31.10.2022, but as soon as I add the formulas with the DUE´s it doesnt select the correct SnapshotDate anymore. It also set´s all values in other measures like DUE_30 and DUE_0 to 0.

Can you advise how to solve this issue?

Thank you!

Re: Where function is not working as expected

Aurelien_Martinez — Thu, 10 Nov 2022 10:14:45 GMT

Hi,

Try with parenthesis

Where (DUE_90>=200000 or DUE_60>=500000) and SnapshotDate=date(date#(monthend(now(),-1),'YYYY-MM-DD'),'YYYY-MM-DD');

Re: Where function is not working as expected

JohannesBoehmer — Thu, 10 Nov 2022 13:35:45 GMT

Hi,

first problem solved thanks but it´s still setting all value in DUE_30 and DUE_0 to zero. This is because my data have rows with same dimension information but different values. When I try to add sum(DUE_90) it shows invalid expression.

Re: Where function is not working as expected

vinieme12 — Fri, 11 Nov 2022 04:36:05 GMT

The WHERE clause doesn't set any values it is only for filtering the data,

Check your data at source it would actually be 0 for the given conditions