Need urgent help in previous week set expression

[praveensp]

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

[andrey_krylov]

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,'#.##')

[praveensp]

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?

[andrey_krylov · Accepted solution]

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]