topic Re: Expression help in QlikView

Expression help

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please can you help with the following expression.

I would like to add an IF clause for when Period is JAN-17 else 0.

=Sum(unpriced_lots)-((Sum(unpriced_lots)/NetWorkDays(today(),final_pricing_dt))*(NetWorkDays(today()-1,final_pricing_dt)-3))

Thanks,

Daniel

Re: Expression help

sunny_talwar — Thu, 12 Jan 2017 17:54:05 GMT

May be this:

If(MonthStart(Period) = MakeDate(2017, 1, 1), Sum(unpriced_lots)-((Sum(unpriced_lots)/NetWorkDays(today(),final_pricing_dt))*(NetWorkDays(today()-1,final_pricing_dt)-3)), 0)

or you might be able to use set analysis also.

Re: Expression help

Oleg_Troyansky — Thu, 12 Jan 2017 18:08:29 GMT

This expression only works if the field Period has only one possible value.

If there are many periods and you want to only calculate the sum for one particular period, then the same IF() condition (or a Set Analysis condition) needs to be added within each one of the aggregation functions mentioned here.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

Re: Expression help

danielnevitt — Fri, 13 Jan 2017 10:02:17 GMT

Thanks for the replies.

Sunny - unfortunately the code doesn't work as I believe the period is actually a text field. Is it possible to incorporate JAN-17 in the expression.

Daniel

Re: Expression help

sunny_talwar — Fri, 13 Jan 2017 10:28:48 GMT

May be this:

If(Period = 'JAN-17', Sum(unpriced_lots)-((Sum(unpriced_lots)/NetWorkDays(today(),final_pricing_dt))*(NetWorkDays(today()-1,final_pricing_dt)-3)), 0)

Re: Expression help

Anonymous — Fri, 13 Jan 2017 10:59:06 GMT

if(Period ='JAN-17',Sum(unpriced_lots)-((Sum(unpriced_lots)/NetWorkDays(today(),final_pricing_dt))*(NetWorkDays(today()-1,final_pricing_dt)-3)) ,0)

Re: Expression help

danielnevitt — Fri, 13 Jan 2017 11:35:10 GMT

Thanks for your help, that worked perfectly.

Last question I have is, is there a way of calculating the last working day from a date.

So, I would like to say final_pricing_dt - 3 networkdays. Final_pricing_dt is 28/02/2017, therefore I would expect the result to be 24/02/2017.

Thanks

Daniel

Re: Expression help

sunny_talwar — Fri, 13 Jan 2017 11:47:29 GMT

You can use a FirstWorkDay Function (Using FirstWorkDate() function in QlikView - QlikView Blog, QlikView Videos and Tips at QlikShare)

=FirstWorkDate(MonthEnd(Today(), 1), 3)

Re: Expression help

danielnevitt — Fri, 13 Jan 2017 11:52:17 GMT

Thank again Sunny.

Is it possible to incorporate the field final_pricing_dt, as this might not always be the last day of the month.

Regards,

Daniel

Re: Expression help

sunny_talwar — Fri, 13 Jan 2017 11:57:29 GMT

The above was an example, you can add your field name in place of the Today() function

=FirstWorkDate(MonthEnd(final_pricing_dt), 3)