topic Re: Logic in QlikView

Logic

panipat1990 — Mon, 26 Jan 2026 18:19:17 GMT

Dear Team,

I stuck a issue. My Requirement is

Suppose we have a Sale Data from Apr to Jul

And I want to Result

When a person select May's any date then Sale would be Sum of From (01/04/2016 to 30/04/2016) Sale Showed in Report.

But When i select Apr's Month Any Date then it Showing All Sale.

But it should be sum of (01/04/2016 to 31/03/2016) Sale Zero.

but it is showing All Sale Value. Kindly Help me only for Solevingthis Requirement.

For this i have make two Variable

1. V_End_Month= Date(MonthStart(RangeMin(Date(Floor(Max(Link_Date)),'DD/MM/YYYY'),Date(Today()-1,'DD/MM/YYYY')),0)-1,'DD/MM/YYYY')

2.v_YearStart= DATE(MonthStart(YEARSTART(vCurrentDate,0,4)),'DD/MM/YYYY')

For Making vCurrent Date I use this Formula

vCurrentDate=DATE(RANGEMIN(DATE(FLOOR(MAX(Link_Date)),'DD/MM/YYYY'),DATE(TODAY()-1,'DD/MM/YYYY')),'DD/MM/YYYY')

And I am using Expression in frontend

=Sum({$<Link_Date={"<=$(V_End_Month)>=$(vYearStart)"},[Fiscal Year],[FY Year Name],[Fiscal Quarter],[Fiscal Month]>} Net_Value)

For this i am sharing excel.And Qvw Maker

Re: Logic

vinieme12 — Sat, 26 Nov 2016 15:51:11 GMT

In Addition to below Add a master calendar for flagging Current Fiscal Year and add it your Set Expression

Script

LOAD Link_Date,

Net_Value,

Product,

NUM(Month(Link_Date),'##') as MonthIS,

NUM#(Year(Link_Date),'####') as YearIS

FROM

(ooxml, embedded labels, table is Sheet1);

Dimension

Product

Expression

sum({<Link_Date=,MonthIS = {'$(=month(Max(Link_Date))-1)'}

,CurrFiscalFlag = {1}>} Net_Value)

Re: Logic

panipat1990 — Sat, 26 Nov 2016 16:25:14 GMT

Dear sir,

where will come from Currfiscalflag .kindly send me snap shot

Re: Logic

vinieme12 — Sat, 26 Nov 2016 16:55:22 GMT

See attached App

Re: Logic

panipat1990 — Sat, 26 Nov 2016 16:59:59 GMT

But it show separate month sale . .but I want sale sum from year start......

like if I select 5May then sale sum of from 01/04/2016 to 3 Apr . .

If I select 5Jun then sale sum of from 01/04/2016 to 30may....

problem is coming if I select any date of April then it show current year sale . ....

Re: Logic

vinieme12 — Sat, 26 Nov 2016 17:08:52 GMT

complete expression

sum({<Link_Date=,MonthIS = {'<$(=month(Max(Link_Date)))'}

,fYear = {'$(=if((month(Max(Link_Date))-1)<4,Year(Link_Date),Year(Link_Date)+1))'}

>}Net_Value)

Re: Logic

panipat1990 — Sat, 26 Nov 2016 17:51:14 GMT

If I add date 07-01-2017 then it showed 0....

Re: Logic

vinieme12 — Sat, 26 Nov 2016 18:15:39 GMT

sum({<Link_Date=,fMonth = {"<$(fMonth)"}

,fYear = {'$(=if((month(Max(Link_Date))-1)<4,Year(Link_Date),Year(Link_Date)+1))'}

>}Net_Value)

Re: Logic

panipat1990 — Sat, 26 Nov 2016 18:15:40 GMT

Sir,This expression not working.

Re: Logic

vinieme12 — Sat, 26 Nov 2016 18:43:40 GMT

sum({<Link_Date=,fMonth = {"<$(=if(fMonth=1,13,fMonth))"}

,fYear = {'$(=if((month(Max(Link_Date))-1)<4,Year(Link_Date),Year(Link_Date)+1))'}

>}Net_Value)

Re: Logic

panipat1990 — Sat, 26 Nov 2016 18:45:49 GMT

Not working

Re: Logic

vinieme12 — Sat, 26 Nov 2016 18:53:04 GMT

See attached updated APP

Re: Logic

panipat1990 — Sat, 26 Nov 2016 19:19:56 GMT

Not Working this expression

Re: Logic

vinieme12 — Sat, 26 Nov 2016 19:49:20 GMT

can you share your sample app! it's working for me, have you seen the attachment?