topic Re: Sum If date condition in QlikView

Sum If date condition

davyqliks — Fri, 15 Nov 2024 23:45:33 GMT

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

Re: Sum If date condition

Anil_Babu_Samineni — Thu, 22 Oct 2020 11:10:04 GMT

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?

Re: Sum If date condition

davyqliks — Thu, 22 Oct 2020 12:49:36 GMT

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

Re: Sum If date condition

Kushal_Chawda — Thu, 22 Oct 2020 13:37:19 GMT

@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]))

Re: Sum If date condition

davyqliks — Thu, 22 Oct 2020 13:45:55 GMT

Hi,

Can you help me to fix @Kushal_Chawda

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

Thanks

Daniel

Re: Sum If date condition

Kushal_Chawda — Thu, 22 Oct 2020 13:52:12 GMT

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

Re: Sum If date condition

davyqliks — Thu, 22 Oct 2020 14:30:15 GMT

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

Re: Sum If date condition

davyqliks — Wed, 04 Nov 2020 16:23:46 GMT

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

Re: Sum If date condition

Kushal_Chawda — Wed, 04 Nov 2020 16:31:03 GMT

@davyqliks try below

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

Re: Sum If date condition

davyqliks — Wed, 04 Nov 2020 16:52:04 GMT

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.

Re: Sum If date condition

Brett_Bleess — Wed, 04 Nov 2020 18:45:46 GMT

@davyqliks It would likely be most helpful at this point if you attach a sample QVW file with the issue, so folks can dig into things a bit more deeply, believe that is the best avenue forward on this one to ensure you get a good solution.

Regards,
Brett

Re: Sum If date condition

davyqliks — Fri, 13 Nov 2020 10:14:14 GMT

Hi All,

To update i ended up using an if flag True false in the load and using this in set analysis.

Thanks for your help.

Load

if( (Today()-[Last Order Date])>=90,True(),False() )as [RO>90FLAG],

expression in chart

Sum({< [RO>90FLAG] = {'-1'} >}[Stock Estimated Balance RO] * ([RO Invoice Item Unit Price] * 0.9021000 ))

I hope this may help somebody going forward.

Daniel