Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
JohannesBoehmer
Contributor II
Contributor II

Where function is not working as expected

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!

Labels (1)
1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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');

 

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

3 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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');

 

Help users find answers! Don't forget to mark a solution that worked for you!
JohannesBoehmer
Contributor II
Contributor II
Author

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.

 

vinieme12
Champion III
Champion III

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

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.