As on Date

[siva0606]

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

[nilesh_gangurde]

Hi,

Try this.

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

-Nilesh

[#3]

Hi,

Use in set analysis,

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

HTH

-Shruti

[jagan]

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.

[neha_shirsath]

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

[santharubban]

Try This

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

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

this will help you