Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
praveensp
Contributor
Contributor

Need urgent help in previous week set expression

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.

dATA.PNG

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

Thanks,

Praveen Prabhu

1 Solution

Accepted Solutions
andrey_krylov
Specialist
Specialist

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]

View solution in original post

3 Replies
andrey_krylov
Specialist
Specialist

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
Contributor
Contributor
Author

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
Specialist
Specialist

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]