topic Custimised Variable in QlikView

Custimised Variable

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

Hi Experts,

I have on scenario where I developed on dashboard which is monthly.So when I reload in this month It should show Previous month Data.In my case i want to create one variable.Mu user requirement is like this.In previous month exclude all sunday. Then from month end to minus 10 days .It should fall in any day .Then that days nearest Monday should peek.If 10th day is fall on Monday so it means 10 days are not complete so We should take prior Monday date.

For Eg:-

In september 2017 Month end is 30th september. After Exclude Sunday My 10th day would be 19th Sep and it was Tuesday.So should peek nearest Monday and that will be 18th Sep.But In August My 10th day falls on Monday which 21st august So in this case i should take previous monday date 14th august.I need some idea to handle this.Below logic is failed for June month

vDate=if(weekday(date(Monthend(addmonths(Today(),-2))-11))<>'Mon',Weekstart(date(Monthend(addmonths(Today(),-2))-11)),date(date(Monthend(addmonths(Today(),-2))-11)-7))

Regards,

Rupali Ethape

kaushik.solanki‌ @antoniotiman stalwar1‌

Re: Custimised Variable

kaushiknsolanki — Mon, 02 Oct 2017 09:34:37 GMT

Hi,

Find below the calculation

FindDate:

Load *,If(Num(WeekDay(PrevME-11)) = 1,PrevME-11-7,PrevME-11-(if(Num(WeekDay(PrevME-11))=0,6,Num(WeekDay(PrevME-11))-1))) as FinalDate ;

Load Floor(MonthEnd(AddMonths(Today(),-1))) as PrevME

AutoGenerate 1;

Let vDate = peek('FinalDate',0,'FindDate');

Change the Addmonthsn parameter to -2 to go previous to previous month.

Regards,

Kaushik Solanki

Re: Custimised Variable

swuehl — Mon, 02 Oct 2017 09:39:05 GMT

Please avoid creating duplicate threads for the same topic. It bloats the forum and makes it hard to follow a discussion.

Customized Variable