Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

praveensp
New 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
Contributor III

Re: Need urgent help in previous week set expression

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]

3 Replies
andrey_krylov
Contributor III

Re: Need urgent help in previous week set expression

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

Re: Need urgent help in previous week set expression

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

Re: Need urgent help in previous week set expression

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]

Community Browser