Set Analysis for Current week & previous week

[#1]

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

[Anonymous]

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

[#3]

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.

[jagan]

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.

[#5]

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.

[Anonymous]

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?

[#7]

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.

[sasikanth]

hi

Try some thing like ,

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

then pivot the Dim

may help you

[#9]

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.