Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

siva0606
New Contributor II

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

Please help me in solving this issue.

Regards,

Sivasu

5 Replies
nilesh_gangurde
Valued Contributor

Re: As on Date

Hi,

Try this.

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

-Nilesh

Not applicable

Re: As on Date

Hi,

Use in set analysis,

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

HTH

-Shruti

MVP
MVP

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.

neha_shirsath
Valued Contributor

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)

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
Contributor II

Re: As on Date

Try This

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

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

this will help you

Community Browser