Expression help

[danielnevitt]

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

[sunny_talwar]

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.

[Oleg_Troyansky]

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!

[danielnevitt]

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

[sunny_talwar · Accepted solution]

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)

[Anonymous]

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

[danielnevitt]

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

[sunny_talwar]

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

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

[danielnevitt]

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

[sunny_talwar]

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

=FirstWorkDate(MonthEnd(final_pricing_dt), 3)