topic Re: Week to date expression in QlikView

Week to date expression

Thu, 07 Mar 2013 07:59:08 GMT

Hello,

For a report that I am making I need the following:

I have a graph, which shows our sales over the weeks, and I want to show a cummulative measure which sums from week 1 to the current week in the graph

, see image:

The Blue line is the WTD, I only calculated it in my dataset, which makes the query very slow. Is there a way to let Qlikview calculate the WTD, and how can I do that? When I need to make a WTD measure in my dataset for every cumulative measure, the performance will be dramatic.

Thanks!

Re: Week to date expression

sushil353 — Thu, 07 Mar 2013 08:17:26 GMT

Try to use

MAKEWEEKDATE function in qlikview

for this you should have week no and year.

HTH

Sushil

Re: Week to date expression

Thu, 07 Mar 2013 10:46:06 GMT

Hi Sushil,

This is not what I mean. I want QlikView to calculate the cummulative graph, so it has to sum week 201201 to the current week in the graph. e.g. for week 201210 it has to sum week 201201 to 201210, for week 201212: 201201-201212. In year 2013 it has to start summing as of week 201301.

Re: Week to date expression

Fri, 15 Mar 2013 11:27:39 GMT

No one with an answer? 😞

Re: Week to date expression

kaushiknsolanki — Fri, 15 Mar 2013 11:34:11 GMT

Hi,

You can simply create a new field against your data. Meaning consider you have following data.

Sales from 01/01/2011 till date and your script looks something like this.

load Date, Sales From xyz.

then try doing like this.

load Date,Weekstart(Date) as WeekStartDate,Sales From xyz.

Now use the WeekStartDate in your chart.

Hope the idea is clear.

Regards,

Kaushik Solanki

Re: Week to date expression

Fri, 15 Mar 2013 11:38:53 GMT

Can you upload your sample application

Re: Week to date expression

Fri, 15 Mar 2013 11:44:45 GMT

No I can't since it contains confidential information.

Re: Week to date expression

Fri, 15 Mar 2013 11:46:12 GMT

ok Whats your expression?

Re: Week to date expression

Fri, 15 Mar 2013 11:53:36 GMT

None...The graph needs to do something like this in MDX:

sum({YTD(Calendar.Year.Currentmember, Calendar.CurrentMember)} Sales)

Re: Week to date expression

Fri, 15 Mar 2013 11:55:51 GMT

Calendar.Year.Currentmember?

Calendar.CurrentMember?

Re: Week to date expression

Fri, 15 Mar 2013 11:57:33 GMT

MDX -->

Calendar = Hierarchy

Year = Level in hierarchy

Currentmember = current location in hierarchy

Re: Week to date expression

Fri, 15 Mar 2013 12:02:17 GMT

These expression is satisfied?

Re: Week to date expression

Fri, 15 Mar 2013 12:06:38 GMT

No, this is an expression which I have build in OLAP, and I want the same functionality in QlikView. I do not know how this can be acheived. Sum({YTD(Calendar.Year.Currentmember, Calendar.CurrentMember)} Sales) is a MDX query statement. I want to translate this to a QlikView statement.

Re: Week to date expression

Fri, 15 Mar 2013 12:14:46 GMT

You have any field name for week in qlikview or have to create?