topic Re: Calculated Dimension Giving Days in a Month in QlikView

Calculated Dimension Giving Days in a Month

peter_burgess — Thu, 26 May 2016 23:11:50 GMT

Hi.

I have a date field, called 'Date'. It is populated with individual days for two complete months (e.g. 01 Apr 2016 through 31 May 2016).

I have a variable, vMonth2, which is the MonthName of the latter month (e.g. May 2016).

I have been trying to create a calculated dimension for a line chart, that gives all the days of the second month (e.g. 01 May 2016 through 31 May 2016) regardless of selections in other dimensions. In essence, I want all of the dates to show along the X axis, whether there are corresponding data points for those dates or not.

I started out with this calculated dimension: =If(MonthName(Date)='$(vMonth2)',Date,null())

But it falls over as soon as a selection is made in another dimension (let's say for 'Sales') where there are no corresponding data points for the last few days of the month. The date dimension ends at, for example, 24 May, not 31 May.

If I instruct the chart expression (=sum(Sales)) to allow null values, the line goes bonkers because it then includes Sales for all dates of the previous month (April).

So ... I stand to be corrected, but have decided I probably need to include an aggr() function in my calculated dimension, and somehow instruct it to step through all dates belonging to the complete set of dates applicable to May 2016.

But I cannot get the syntax correct. Can any of you give me an idea of what that should look like, or otherwise suggest a way around the problem?

Thanks,

Pete

Re: Calculated Dimension Giving Days in a Month

sunny_talwar — Fri, 27 May 2016 16:15:46 GMT

Would it be possible to look at the issue? Essentially if you can provide a sample, we might be able to help you in much better way

Re: Calculated Dimension Giving Days in a Month

settu_periasamy — Fri, 27 May 2016 16:29:33 GMT

If you want to ignore other dimension selection, may be you can try to use the set expression with your ignoring fields.. like

=sum ({<Year,Date,Sales>}Sales)

Re: Calculated Dimension Giving Days in a Month

peter_burgess — Sun, 29 May 2016 04:49:10 GMT

Thanks Sunny and Settu.

Settu has put me on the right track. I have incorporated the set selection of all possible Dates in my expressions for my measures, and by leaving the calculated dimension as it is have achieved the result I require.

In simplified form, the expression for Sales now looks like this: =sum({1 <Date=>} Sales)

I believe I can tweak the calculations for Dimension and Measures a bit more so they become a little more robust, but now I am much more confident of achieving the final desired outcome.

Regards,

Pete

Re: Calculated Dimension Giving Days in a Month

sunny_talwar — Sun, 29 May 2016 06:00:28 GMT

I don't understand the point of using 1 and <Date = > at the same time? Are you looking to ignore all selections or just selection in Date field? If you want to ignore all selections use this -> Sum({1} Sales) and if you are looking to ignore selections in just Date use this -> Sum({<Date = >}Sales)

Re: Calculated Dimension Giving Days in a Month

peter_burgess — Sun, 29 May 2016 23:31:23 GMT

Sunny –

I have data coming in from Sales as follows:

Date Sales (Channel 1, cumulative)

01/05/16 0

02/05/16 85

03/05/16 125

04/05/16 150

05/05/16 190

06/05/16 255

07/05/16 255

Note this is a simplified table – there are actually several columns comprising data from different Channels.

But the table as received only includes data up to the current date.

I have a separate Dates table that displays all dates, from 01/04/16 through to 31/05/16

Date

01/04/16

02/04/16

03/04/16

04/04/16

. . .

01/05/16

02/05/16

03/05/16

. . .

30/05/16

31/05/16

In my QV document, the user makes a selection on Sales Channel, this automatically associates Dates with it.

If I use just the sum({<Date=>} Sales expression, the Dates are still limited to 01/05/16 through 07/05/16.

If I use the sum({1 <Date=>} Sales expression, I include ALL possible Dates. Because I have a calculated Dimension that specifies the dates for the full month of May, the displayed Dates are limited to just May, which is what I want.

Fig 1 – using modifier {<Date=>} Fig 2 – using modifier {1 <Date=>}

Hope this helps explain my rationale.

I guess I could have worked out a better data model, but at the time it satisfied the purpose.

Regards,

Pete

Re: Calculated Dimension Giving Days in a Month

peter_burgess — Mon, 30 May 2016 01:21:47 GMT

Hi all.

I see that the graphs I had included in my last post didn't make it through. I reproduce them below. Figure 1 is the result of using the modifier {<Date=>}, Figure 2 is using {1 <Date=>}.

Figure 1

Figure 2

Regards,

Pete

Re: Calculated Dimension Giving Days in a Month

sunny_talwar — Mon, 30 May 2016 09:45:48 GMT

What is your calculated dimension look like? Curious to see how that looks

Re: Calculated Dimension Giving Days in a Month

peter_burgess — Mon, 30 May 2016 20:46:05 GMT

Sunny -

As mentioned in my first post, the calculated dim is: =If(MonthName(Date)='$(vMonth2)',Date,null()).

Where vMonth2 is a variable, in this case assigned with 'May 2016'.

Re: Calculated Dimension Giving Days in a Month

sunny_talwar — Mon, 30 May 2016 21:57:10 GMT

How about this then?

=Sum({<[Sales Channel]>} Sales)

=Sum({<[Sales Channel], Date>} Sales)

UPDATE: The only reason I am not satisfied with using Sum({1<Date>} Sales) is because it looks redundant to use Date if you are already using 1 in your set analysis. 1 restrict any selection and hence date if probably not needed. But in your case you seems to want to avoid selection in Sales Channel and may be Date.

Re: Calculated Dimension Giving Days in a Month

peter_burgess — Fri, 03 Jun 2016 01:12:36 GMT

Sunny –

Thanks. I have decided to re-design my data model, and will look at your suggestion once I have completed the transformation.

I will advise the Community if and when I reach a satisfactory outcome.

Regards,

Pete