topic Re: As on Date in QlikView

As on Date

siva0606 — Tue, 28 Jan 2014 05:25:19 GMT

Hi all,

I am new to qlikview.

I want to build a expression by following condition

For example, I have number of items and i want to see the sales of that items.

If i click Year as 2013 and month as Jun , it should show me the sales from 2013 Jan to Jun.

Similarly , if i click Year as 2013 and month as Nov, it should show me the sales from 2013 Jan to Nov.

My expression doesnt satisfy the above condition.

I have set variable vcurrentyear=Max(Year) and vmonth= Max(Month)

and my expression is Sum(if(Year=vcurrentyear and Month=vmonth,SalesAmount)).

Please help me in solving this issue.

Regards,

Sivasu

Re: As on Date

nilesh_gangurde — Tue, 28 Jan 2014 05:28:44 GMT

Hi,

Try this.

Sum( {<Year = {"$(=Max(Year))"}, Month = {"<=$(=max(Month))"}>} SaleAmount)

-Nilesh

Re: As on Date

Tue, 28 Jan 2014 05:47:57 GMT

Hi,

Use in set analysis,

Sum({<Year= {"$(vcurrentyear)"}, Month= {''<=$(vcurrentyear)"}>} Amount)

HTH

-Shruti

Re: As on Date

jagan — Tue, 28 Jan 2014 05:57:33 GMT

Hi,

If you have a date field in your data model then try like this

=Sum({<MonthDimensionName=, DateDimensionName={'>=$(=YearStart(Max(DateDimensionName)))<=$(=Date(Max(DateDimensionName)))'}>} MeasureName)

MonthDimensionName=, DateDimensionName, MeasureName replace with your corresponding dimension and measure names.

Regards,

Jagan.

Re: As on Date

neha_shirsath — Tue, 28 Jan 2014 06:53:54 GMT

Hi,

Try the below expression-

sum({$<NewDate={">=$(=(date((finyear),'MM/YYYY')))<=$(=(date((max(NewDate)),'MM/YYYY')))"},finyear=,

Year=, CalendarMonth=, CalendarQuarter=>}Sales)

where your finyear will be-

YearName(date(NewDate,'MM/YYYY'),0,1) as finyear

add this in script.

Hope this will help you.

-Neha

Re: As on Date

santharubban — Fri, 31 Jan 2014 17:36:08 GMT

Try This

(count({<CreatedDateId = {">=$(=(v_MonthStartDate))<=$(=(Variable5))"}>}OrderLineNo)

v_MonthStartDate =date(YearStart(date#(DateId,'YYYYMMDD')),'YYYYMMDD')
Variable5=date#(DateId,'YYYYMMDD')

this will help you