topic Re: Date Calculation in QlikView

Date Calculation

khasimvali85 — Tue, 05 Jan 2016 11:18:13 GMT

Hi Community,

I have date field and sales data, i have to create bar chart and the dimension is date field, sales are using sum(sales).

my requirement is the date field as dimension like to create current month, previous month, last year current month.

i need only use calculated dimensions using Date Field as per data

1, Current Month and Current Year (June-2015)

2, Previous Month and Current Year(May-2015)

3, Last Year Current Month(June-2014)

how do I achieve this?

Ex: Date field is Date

Expression in SUM(Sales)

The chart like attachment

Re: Date Calculation

rubenmarin — Tue, 05 Jan 2016 11:24:45 GMT

Hi, you can create 3 expressions, without dimensions, using set analisys to select the periods for each expression.

In the label of each expression you can create the expression that returns the month-year of each bar.

Re: Date Calculation

sunny_talwar — Tue, 05 Jan 2016 11:34:53 GMT

Try this:

Dimension: =MonthName(DATE)

Expression:

=Sum({<DATE = {"$(='>=' & Date(MonthStart(Max(DATE)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(Max(DATE)), 'DD-MM-YYYY'))"} +

{"$(='>=' & Date(MonthStart(AddMonths(Max(DATE), -1)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(AddMonths(Max(DATE), -1)), 'DD-MM-YYYY'))"} +

{"$(='>=' & Date(MonthStart(AddYears(Max(DATE), -1)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(AddYears(Max(DATE), -1)), 'DD-MM-YYYY'))"}>}SALES)

Re: Date Calculation

sasikanth — Tue, 05 Jan 2016 11:35:25 GMT

Instead you can write all expressions ,

please find the attachment

Exp 1) =sum({<New_Date= {$(=date(max(New_Date),'YYYYMM'))}>}SALES)

Exp 2) =sum({<New_Date= {$(=date(addmonths(max(New_Date),-1),'YYYYMM'))}>}SALES)

Exp 3)= sum({<New_Date={$(=date(AddYears(max(New_Date),-1),'YYYYMM'))}>}SALES)

Re: Date Calculation

khasimvali85 — Tue, 05 Jan 2016 11:36:08 GMT

Hi Ruben,

Thanks for suggestion, i need in Dimension wise these date fields.

how do i achieve this?

can you suggest me?

Re: Date Calculation

rubenmarin — Tue, 05 Jan 2016 11:47:23 GMT

I can but as both Sunny's provided a working solution you can choose one of those more detailed answers.

Re: Date Calculation

sunny_talwar — Tue, 05 Jan 2016 11:56:02 GMT

Hahahaha

Re: Date Calculation

khasimvali85 — Tue, 05 Jan 2016 12:05:33 GMT

Hi Sunny,

Thanks for suggestion

Re: Date Calculation

sunny_talwar — Tue, 05 Jan 2016 12:22:11 GMT

No offense Khasimvali85‌, but all three of us provided you with some type of solution/suggestion. How is this a 'Assumed Answer'? Please reward people who are trying to help you. The 'Correct Answer' and 'Helpful Answer' are there for a reason.

Best,

Sunny