topic Re: Need urgent help in previous week set expression in QlikView

Need urgent help in previous week set expression

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

Hi,

I need help on previous week calculation. I am trying to calculate a percentage value for previous week using below expression.

num((((sum({$<[received_date]={">=$(=Date(WeekStart(Max([received_date]),-1, 0),'YYYY-MM-DD'))<=$(=Date(WeekEnd(Max([received_date]),-1, 0),'YYYY-MM-DD'))"}>}[shop_1])+sum({$<[received_date]={">=$(=Date(WeekStart(Max([received_date]),-1, 0),'YYYY-MM-DD'))<=$(=Date(WeekEnd(Max([received_date]),-1, 0),'YYYY-MM-DD'))"}>}[shop_2]))/sum({$<[received_date]={">=$(=Date(WeekStart(Max([received_date]),-1, 0),'YYYY-MM-DD'))<=$(=Date(WeekEnd(Max([received_date]),-1, 0),'YYYY-MM-DD'))"}>}[Total Build])))*100,'#.##')

But i am not getting any answer when applied this expression.

In simpler terms without the set analysis, it is (sum(shop_1)+sum(shop_2))/sum(total_build) for previous week.

Below is my data for previous week and i have highlighted in a circle, the answer i am looking for.

Kindly help me in resolving the above issue as i am very new to Qlikview.

Thanks,

Praveen Prabhu

Re: Need urgent help in previous week set expression

andrey_krylov — Tue, 09 Oct 2018 18:26:29 GMT

Hi Praveen. I think you should to change the date format

num((((sum({$<[received_date]={">=$(=Date(WeekStart(Max([received_date]),-1, 0),'M/D/YYYY'))<=$(=Date(WeekEnd(Max([received_date]),-1, 0),'M/D/YYYY'))"}>}[shop_1])+sum({$<[received_date]={">=$(=Date(WeekStart(Max([received_date]),-1, 0),'M/D/YYYY'))<=$(=Date(WeekEnd(Max([received_date]),-1, 0),'M/D/YYYY'))"}>}[shop_2]))/sum({$<[received_date]={">=$(=Date(WeekStart(Max([received_date]),-1, 0),'M/D/YYYY'))<=$(=Date(WeekEnd(Max([received_date]),-1, 0),'M/D/YYYY'))"}>}[Total Build])))*100,'#.##')

Re: Need urgent help in previous week set expression

praveensp — Thu, 11 Oct 2018 09:13:01 GMT

Thanks for the reply Andrey.

But the date format is changed when put them in excel.

the date format in qlikview is like - 2018-10-08 00:00:00.000000

Is this causing the issue?

Re: Need urgent help in previous week set expression

andrey_krylov — Thu, 11 Oct 2018 19:33:57 GMT

It seems so. Try this date format in expression 'DD-MM-YYYY hh.mm.ss.ffffff' or load

Date(Floor([received_date]), 'DD-MM-YYYY') as [received_date]