topic Re: how to create calculated dimensions creation? in QlikView

how to create calculated dimensions creation?

khasimvali85 — Mon, 03 Aug 2015 11:48:46 GMT

Hi,

Can anybody give idea about calculate dimensions

how to create calculated dimension with date field like given below

i have date field like

date

jan-14, feb-14, mar-14 up to current month aug-15

my requirement is in bar chart dimensions like given attachment

current month , previous month and last year current month

Regards,

khasim.

Re: how to create calculated dimensions creation?

marcus_sommer — Mon, 03 Aug 2015 11:55:47 GMT

This will be helpful: Calculated Dimensions.

- Marcus

Re: how to create calculated dimensions creation?

Mon, 03 Aug 2015 12:01:04 GMT

Please find attachment

Re: how to create calculated dimensions creation?

jonathandienst — Mon, 03 Aug 2015 12:02:50 GMT

I would not use calculated dimensions for this. I would create a selectable date range field. A script like this will add date ranges to the model which you can use in set expressions.

First, the script:

Let zToday = Today(1);

Ranges:

LOAD Range, Date(Evaluate(RangeStart)) As RangeStart, Date(Evaluate(RangeEnd)) As RangeEnd;

LOAD * INLINE [

Range; RangeStart; RangeEnd

Today; zToday; DayEnd(zToday)

Yesterday; zToday-1; DayEnd(zToday-1)

This Week; WeekStart(zToday); WeekEnd(zToday)

Last Week; WeekStart(zToday,-1); WeekEnd(zToday, -1)

Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(zToday)

This Month; MonthStart(zToday); MonthEnd(zToday)

Last Month; MonthStart(zToday,-1); MonthEnd(zToday,-1)

This Month Last Year; MonthStart(zToday,-12); MonthEnd(zToday, -12)

Last Month Last Year; MonthStart(zToday,-13); MonthEnd(zToday, -13)

Last Two Months; MonthStart(zToday,-1); MonthEnd(zToday)

This Year; YearStart(zToday); YearEnd(zToday)

Last Year; YearStart(zToday,-1); YearEnd(zToday,-1)

YTD; YearStart(zToday); DayEnd(zToday-1)

Last YTD; YearStart(zToday,-1); AddYears(DayEnd(zToday-1), -1)

MTD; MonthStart(zToday); DayEnd(zToday - 1)

Last MTD; MonthStart(zToday, -1); AddMonths(DayEnd(zToday-1), -1)

Last Year MTD; MonthStart(zToday, -12); AddMonths(DayEnd(zToday-1), -12)

] (delimiter is ';');

JOIN (Ranges) IntervalMatch (CalDate) LOAD RangeStart, RangeEnd RESIDENT Ranges;

DROP FIELDS RangeStart, RangeEnd;

The last step joins the ranges to your calendar. Replace CalDate in this line with the name of your calendar date field.

Now use the ranges in the front end

=Sum({<Range = {'Yesterday'}>} Sales)

=Count(<Range = {'Last Month'}>} distinct EmployeeID)

=Avg({<Range = {'MTD'}>} Receipt)

Re: how to create calculated dimensions creation?

sorrakis01 — Mon, 03 Aug 2015 12:10:29 GMT

Hi,

I would not use calculated dimensions for this.

I would create a 3 set analysis expresions to resolve your problem.

1 exp: Sum(Value) (Month selected)

2 exp: Sum(Value) of previous Month

3 exp: Sum (Value) of Last Year Month

Regards

Re: how to create calculated dimensions creation?

qlikmsg4u — Mon, 03 Aug 2015 12:25:32 GMT

It is better to create set analysis expressions rather than calculated dimensions.

Re: how to create calculated dimensions creation?

Kushal_Chawda — Mon, 03 Aug 2015 12:51:34 GMT

Please see the attached.

By default it will display data for current month. You can select the any other month to get the data as required

Re: how to create calculated dimensions creation?

antoniotiman — Mon, 03 Aug 2015 12:59:56 GMT

Hi,

Dimension :

MonthName(Date)

Expression :

=Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=Max(Date))"}+

{">=$(=MonthStart(Max(Date),-12))<=$(=MonthEnd(Max(Date),-12))"}>} Value)

This also works if You have several dates per Month.

Re: how to create calculated dimensions creation?

khasimvali85 — Tue, 04 Aug 2015 03:53:51 GMT

Hi,

Thanks for reply

already i have date field date or Month Year

i need condition in dimension using month year.

this one also helpful but not my exact output

Thanks,

Khasim.

Re: how to create calculated dimensions creation?

khasimvali85 — Tue, 04 Aug 2015 11:26:13 GMT

Hi,

thanks for reply but not like that

the default change the month automatically change the month year and

these values used as dimension

as per given data Last year Current month year(July-14), Previous month year(June-15)) and Current month year(July-15)

Re: how to create calculated dimensions creation?

jonathandienst — Tue, 04 Aug 2015 11:34:17 GMT

Khasimvali85 wrote:

already i have date field date or Month Year

i need condition in dimension using month year.

this one also helpful but not my exact output

This was a sample for you to adapt to your needs

Re: how to create calculated dimensions creation?

khasimvali85 — Tue, 04 Aug 2015 11:41:13 GMT

Hi,

Thanks for reply

can you give example file for this?

Re: how to create calculated dimensions creation?

khasimvali85 — Tue, 04 Aug 2015 11:51:05 GMT

HI,

You created in script level but i need in designing level

already i am having the fields like date field and month year field and value fields

just i need in dimension i don't want all date fields

i need only as per given data Last Year Current Month(July-14) , Current Year Previous month(June-15) and Current year current Month(July-15)

Re: how to create calculated dimensions creation?

antoniotiman — Tue, 04 Aug 2015 11:53:30 GMT

See attachment.

Regards,

Antonio

Re: how to create calculated dimensions creation?

Kushal_Chawda — Tue, 04 Aug 2015 13:56:26 GMT

Khasimvali85‌

i think what I have done is dynamic. You can select the any month.. it will automatically change the dimension.