topic Re: Set Analysis for Current week & previous week in QlikView

Set Analysis for Current week & previous week

Tue, 23 Dec 2014 18:08:46 GMT

Hi All,

I have a problem in displaying the Invoice count & Invoice Amount by Customerwise.

Need to display adjacent to each other.

The results for two weeks,

                30/Nov/2014 - 6/Dec/2014            7/Dec/2014 - 13/Dec/2014
                   Count   Amount                              Count       Amount
CUS001        5             500                                     7            700
CUS002       10           1000                                   12          1200
CUS003       15           1500                                   17          1700

CUStomer &
30/Nov /2014 - 6/Dec/2014 [WeekStart -- WeekEnd]
created in Dimension..

Count & Amount in Expression.

Now in Set Analysis, i am getting the right results. But one issue i found.

My results are like this,

30/Nov/2014 - 6/Dec/2014
Count Amount Count Amount
CUS001 5 500 7 700
CUS002 10 1000 12 1200
CUS003 15 1500 17 1700

Though i have 30/Nov/2014 - 6/Dec/2014 in dimensions, i am not sure how to achieve it.

Thanks in Advance...

Sasikumar.R

Re: Set Analysis for Current week & previous week

Anonymous — Tue, 23 Dec 2014 18:54:47 GMT

Hello!

If you use a pivot table, you can drag week dimension on the top in order to have your expressions on 4 columns.

Can this help you?

Elena

Re: Set Analysis for Current week & previous week

Wed, 24 Dec 2014 02:35:01 GMT

Hi Elena, Thanks for your input.

But the expectation is that, the result should be two set of weeks,

30/Nov/2014 - 6/Dec/2014 7/Dec/2014 - 13/Dec/2014

Count Amount Count Amount

CUS001 5 500 7 700

CUS002 10 1000 12 1200

CUS003 15 1500 17 1700

But the result i am getting is like below,

30/Nov/2014 - 6/Dec/2014

Count Amount Count Amount

CUS001 5 500 7 700

CUS002 10 1000 12 1200

CUS003 15 1500 17 1700

Thanks in advance.

Re: Set Analysis for Current week & previous week

jagan — Wed, 24 Dec 2014 03:45:09 GMT

Hi,

Drag the week column to the top, and you can restrict displaying only two weeks in set Analysis like below

For Amount

=Sum({<DateDimension={'>=$(=WeekStart(Today(), -1))<=$(=WeekEnd(Today()))'}>} Amount)

For Count

=Count({<DateDimension={'>=$(=WeekStart(Today(), -1))<=$(=WeekEnd(Today()))'}>} SomeDimension)

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis for Current week & previous week

Wed, 24 Dec 2014 10:25:57 GMT

Thanks Jagan.

The original Requirement is like below.

The steps which i followed,

I created two dimensions,

1). Customer ID

2). =WeekStart([Invoice Date]) &'----'& WeekEnd([Invoice Date])

Expression,

1).Count

2).Submitted Amount

3).Paid Amount

Now In the Expressions,

=if(match([Invoice Status],'Invoice Created'),Sum({<Week = {"$(=MAX(Week))"}>}[Invoice Total]),

if(match([Invoice Status],'Invoice Rejected'),Sum({<Week = {"$(=MAX(Week))"}>}[Fees Total])))

I am getting the values correctly, The but the week above shown is not split into two weeks.

Do i need to do anything in Dimension for the week display.

Thanks in Advance.

Re: Set Analysis for Current week & previous week

Anonymous — Wed, 24 Dec 2014 10:42:33 GMT

Hi, I tried with some data of mine...I haven't your visualization: see the attachment.

Please, can you share sample data and/or qvw application?

Re: Set Analysis for Current week & previous week

Wed, 24 Dec 2014 10:48:33 GMT

Hi Elena,

The original Requirement is like below.

The steps which i followed,

I created two dimensions,

1). Customer ID

2). =WeekStart([Invoice Date]) &'----'& WeekEnd([Invoice Date])

Expression,

1).Count

2).Submitted Amount

3).Paid Amount

Now In the Expressions,

=if(match([Invoice Status],'Invoice Created'),Sum({<Week = {"$(=MAX(Week))"}>}[Invoice Total]),

if(match([Invoice Status],'Invoice Rejected'),Sum({<Week = {"$(=MAX(Week))"}>}[Fees Total])))

I am getting the values correctly, The but the week above shown is not split into two weeks.

Do i need to do anything in Dimension for the week display.

Thanks in Advance.

Re: Set Analysis for Current week & previous week

sasikanth — Wed, 24 Dec 2014 11:11:47 GMT

Try some thing like ,

WeekStart(ceil(day(Invoice Date]) /7))&'---&WeekEnd(ceil(day(Invoice Date]) /7))

then pivot the Dim

may help you

Re: Set Analysis for Current week & previous week

Wed, 24 Dec 2014 11:18:08 GMT

Hi SasiKanth,

The original Requirement is like below.

The steps which i followed,

I created two dimensions,

1). Customer ID

2). =WeekStart([Invoice Date]) &'----'& WeekEnd([Invoice Date])

Expression,

1).Count

2).Submitted Amount

3).Paid Amount

Now In the Expressions,

=if(match([Invoice Status],'Invoice Created'),Sum({<Week = {"$(=MAX(Week))"}>}[Invoice Total]),

if(match([Invoice Status],'Invoice Rejected'),Sum({<Week = {"$(=MAX(Week))"}>}[Fees Total])))

I am getting the values correctly, The but the week above shown is not split into two weeks.

Do i need to do anything in Dimension for the week display.

Thanks in Advance.