5 Replies Latest reply: Jan 31, 2014 12:39 PM by Santharubban Kumarasamy Shanmugasundaram

As on Date

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)).

Regards,

Sivasu

• Re: As on Date

Hi,

Try this.

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

-Nilesh

• Re: As on Date

Hi,

Use in set analysis,

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

HTH

-Shruti

• Re: As on Date

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

Hi,

Try the below expression-

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

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

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