topic Re: Logic in QlikView

Logic

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

Hi Expertise

I have a line chart Iam using the below expression to display the number of months.

Sum({<Month = {">=$(=Max(Month)-8) <=$(=Num(Month(Today())))"}>} Tickets)

The above expression will display me exactly 9 months and also tested for 9 , 10 , 111 , 12 months accordingly..

But this is not giving me 13 months..

if i use the below condition

Sum({<Month = {">=$(=Max(Month)-13) <=$(=Num(Month(Today())))"}>} Tickets)

by using the above expression still i get the line chart diplayed from 1 to 12 ...where as it has to display from 12 to 12 ( dec-2016 to dec2017)

as shown in the screenshot i have year from 2010 till 2017 and this is the month in number i have ..

in dimension also i am using Month..

How do i achive this ..need your help

Thanks in advance

Re: Logic

devarasu07 — Tue, 12 Dec 2017 10:20:07 GMT

Hi,

for the 13 month rolling you can try to user YearMonth field instead of Month

try to create year month at back end script,

Date(Date(Date#(Month,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth,

and u r set expression like below,

Sum({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -13), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"} >} Tickets)

Thanks,

Deva

Re: Logic

tresB — Tue, 12 Dec 2017 10:23:54 GMT

I would rather use Date field like:

Sum({<Date = {">=$(=MonthStart(Max(Date),-13)) <=$(=MonthStart(Today(),-1))"}>} Tickets)

Re: Logic

smilingjohn — Tue, 12 Dec 2017 10:29:58 GMT

Hi Devarasu ..

I have the below format date ..as shown in screenshot

And iam trying to convert it like this but this year field is showing blank ..

do i need to make any changes ..

Re: Logic

smilingjohn — Tue, 12 Dec 2017 10:45:34 GMT

Hi Devarasu ,,

Iam trying to restrict the Sold Date by using this login in the script is this correct?

It reloads but doesnt show any out put ..

Please suggest

where Sold_Date > date(1/12/2015,'DD/MM/YYYY' and Sold_Date <= date(1/12/2017,'DD/MM/YYYY'));

Re: Logic

YoussefBelloum — Tue, 12 Dec 2017 10:47:03 GMT

Hi,

First you need to create the Month_Year format field date like this:

Date(MakeDate(year,month),'YYYYMM') as Date

after that you need this expression:

Sum({<Date ={">=$(=Date(addmonths(Max(Date), -13),'YYYYMM')) <=$(=Date(addmonths(Max(Date), -1),'YYYYMM'))"} >} data)

find attached:

Re: Logic

devarasu07 — Tue, 12 Dec 2017 10:49:21 GMT

Hi,

Rolling 13 month using set analysis

Sum({<Year=, Quarter=, Month=, Week=, Date={'>=$(=MonthStart(Max(Date), -13))<=$(=Date(Max(Date)))'}>} Tickets)

for the script method you can try restrict data like below,

let hDate = date(today()+(day(today())-1));

let dDate = date(MonthStart(today(),-12));

load * FROM table.qvd (qvd) where date(Date)>=date('$(dDate)') and date(Date)<=date('$(hDate)');

more steps check this thread.

Last 3 months' data in load script