Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
davyqliks
Specialist
Specialist

Sum If date condition

Hi,

I need to only show the last order date if greater than 90 days old.

I have used the condition in the colour function to highlight red and it worked like this:

if(Today()-90>= Only( [Last Order Date UK]),
rgb(255,26,26),)

But i now need to only show the line and date if this condition is met:

I use the below which in my mind looks right with no syntax issue but the date returns 0

if(Today()-90>=,Only({$<Location = {'UKInv'},[Retailer Name] = {'ASOS'}, [Item Default Production Method] = {'In-House(Stock)','Outsource(Stock)'}>}[Last Order Date]))

 

Can someone inform how i can display the date when only older than 90days with this set analysis.

Thank you

 

 

 

Labels (3)
11 Replies
Anil_Babu_Samineni

What it is returning first in KPI? 

Only({$<Location = {'UKInv'},[Retailer Name] = {'ASOS'}, [Item Default Production Method] = {'In-House(Stock)','Outsource(Stock)'}>}[Last Order Date])

And I see your condition is different because there is nothing to compare for your Today()-90?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
davyqliks
Specialist
Specialist
Author

Thank you for taking the time to respond... rather than give you what i did maybe i should just ask..

using this set analysis:

{$<Location = {'UKInv'},[Retailer Name] = {'ASOS'}, [Item Default Production Method] = {'In-House(Stock)','Outsource(Stock)'}>}

 

how would i write the expression to only show the [Last Order Date] when it is over 90 old?

thanks again for your help in advance.

Daniel

 

Kushal_Chawda

@davyqliks  looks syntax issue

if(Today()-90>=Only({$<Location = {'UKInv'},[Retailer Name] = {'ASOS'}, [Item Default Production Method] = {'In-House(Stock)','Outsource(Stock)'}>}[Last Order Date]))

davyqliks
Specialist
Specialist
Author

Hi,

Can you help me to fix @Kushal_Chawda 

i see i have a bracket issue but i cannot resolve.

Thanks

Daniel

Kushal_Chawda

@davyqliks  How do you want to use this expression? In back ground color or as actual expression?

davyqliks
Specialist
Specialist
Author

Hi,

Thanks Kush... As an actual expression please.

At first it was just for colour and i achieved that and tried to adapt the code for the expression but unsuccessfully.

Thanks Kush

davyqliks
Specialist
Specialist
Author

Hi Kush,

I am still looking at my issue. i need to show the value of this sum only if the last order date is over 90 days ago:

if(Today()-90>= ( [Last Order Date RO]),Sum([Stock Estimated Balance RO] * ([RO Invoice Item Unit Price] * 0.9021000 )))

The above is my best effort but making no return. is there any chance you can assist? This is giving no error in the expression but no return. Any help would be much appreciated.

thanks

Daniel

 

Kushal_Chawda

@davyqliks  try below

if(Today()-[Last Order Date RO]>=90,Sum([Stock Estimated Balance RO] * ([RO Invoice Item Unit Price] * 0.9021000 )))

davyqliks
Specialist
Specialist
Author

Thanks Kush,

I'm sorry but i get the error Unknown chart type With a cross through it.


I am using the same expression in a Bar chart without the if date statement like this for multiple expression changing the location.

Sum([Stock Estimated Balance UK] * [UK Invoice Item Unit Price])

This is working fine but i cannot get a return for any location with the prefix if statement.

if(Today()-[Last Order Date RO]>=90,     Sum([Stock Estimated Balance RO] * ([RO Invoice Item Unit Price] * 0.9021000 )))

 

Sorry Kush..... any ideas? thanks a lot.