topic Re: need help for a set analysis in QlikView

need help for a set analysis

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

Hi,

I need to calculate previous week id's total:

Note that I have these following fields in my Data model:

Week Id = 2015-W02 or 2014-W09 like this and

Process Date= 09/31/2017 like this

So I am using the below set analysis, but it gives me nothing:

'Prior Week Total= ' & sum({<[Week ID]={"$((=year([Process Date])) & '-W' & (=num(week([Process Date])-1,'00')))"}>}[Sales Posting Amount USD])

So when I choose week id from drop down it will show previous week's value.

Please help me about this.

Thanks,

Sandip

Re: need help for a set analysis

vishsaggi — Wed, 26 Apr 2017 15:20:30 GMT

Can you share a sample to look into?

Re: need help for a set analysis

devarasu07 — Wed, 26 Apr 2017 16:03:11 GMT

Hi,

I hope your trying to do in Text object, if so just try like below,

='Previous Week Sales: '& Num(Sum({<Date = {">=$(=Week(Max([Process Date]), -1)) <= $(=Date(Max([Process Date]), -7))"}>} TotalNetPriceSGD),'#,##0.00')

Re: need help for a set analysis

devarasu07 — Wed, 26 Apr 2017 16:32:38 GMT

Hi,

using backend method,

1) Try to add below WeekSerial column in your calendar script

AutoNumber(weekyear(Date) &'|' & week(Date) as WeekSerial

2) then use the WeekSearil in front end using set analysis method

sum({<Cal_WeekYear=, Cal_Year=, WeekSerial={$(=max(WeekSerial)-1)}>}

'Prior Week Total= ' & sum({<WeekYear=,Year=, [Week ID]={"$((=year([Process Date])) & '-W' & (=num(week([Process Date])-1,'00')))"}>}[Sales Posting Amount USD])

Hope this helps you

Re: need help for a set analysis

ananyaghosh — Thu, 27 Apr 2017 07:46:51 GMT

Hi,

What is 'Date=' in your expression(in Bold face font)?

Is it a function or column name here:

='Previous Week Sales: '& Num(Sum({<Date = {">=$(=Week(Max([Process Date]), -1)) <= $(=Date(Max([Process Date]), -7))"}>} TotalNetPriceSGD),'#,##0.00')

or it should be as:

Num(Sum({<[Process Date]= {">=$(=Week(Max([Process Date]), -1)) <= $(=Date(Max([Process Date]), -7))"}>} TotalNetPriceSGD),'#,##0.00')

please correct me if I am wrong.

Also I need to use it in a straight table. Will it work there?

Thanks,

Sandip

Re: need help for a set analysis

ananyaghosh — Thu, 27 Apr 2017 07:57:29 GMT

Hi,

Why you are using max(WeekSerial) - means why you are using max() function here?

sum({<Cal_WeekYear=, Cal_Year=, WeekSerial={$(=max(WeekSerial)-1)}>}

Thanks,

Sandip