topic Re: Expression Help - Date in QlikView

Expression Help - Date

Anonymous — Mon, 13 Nov 2017 03:21:03 GMT

Hi All,

I need some help to excluded dates for my sum function. Currently, I use this formula to exclude name where =”Rick”. Sum({<[Type]={'Cost'},Name-={'Rick'}>}[Amount])”. Instead of the Name field I want to use a “Date” field.

What I want to do is exclude dates where it happen during the quarter. In my dashboard, I have “Reporting Date” field such as 3/31/2017, 6/30/2017, and 9/30/2017. For example: if I selected Reporting Date field 6/30/2017, I want the formula to exclude “Date” where Date >= 4/1/2017 and Date <=6/30/2017.

Sum({<[Type]={'Cost'},Date >= 4/1/2017 and Date <=6/30/2017>}[Amount])

Any input greatly appreciated!

Many Thanks,

Frank

Re: Expression Help - Date

shraddha_g — Mon, 13 Nov 2017 04:43:50 GMT

Sum({<[Type]={'Cost'},Date >= 4/1/2017 and Date <=6/30/2017>}[Amount])

If here 4/1/2017 is your year start of selected date (6/30/2017) then,

sum({<[Type] = {'Cost'}, Date = {">=$(=Date(YearStart(Max(Date),0,4),'M/D/YYYY'))<=$(=Date(Max(Date),'M/D/YYYY'))"}>}[Amount])

Re: Expression Help - Date

Anil_Babu_Samineni — Mon, 13 Nov 2017 05:09:03 GMT

In fact, Dates seems static. Would you go same then simply to call. If you notice below statement you could get some minus symbol before equal sign.

Sum({<[Type]={'Cost'},Name-={'Rick'}, Date -= {">=4/1/2017 <=6/30/2017"}>}[Amount])

Sum({<[Type]={'Cost'},Name-={'Rick'}, Date -= {"=Date >= 4/1/2017 and Date <= 6/30/2017"}>}[Amount])

Note - Make sure to clear what you need if dynamic needed.

Re: Expression Help - Date

pradosh_thakur — Mon, 13 Nov 2017 06:48:57 GMT

try this

sum({<[Type] = {'Cost'}, Date -= {">=$(=Date(quarterstart(Max(Date)),'MM/DD/YYYY')) <=$(=Date(Max(Date),'MM/DD/YYYY'))"}>}[Amount])

Re: Expression Help - Date

Anonymous — Mon, 13 Nov 2017 18:54:13 GMT

All,

Thanks for quick responses. Will check it out later.