7 Replies Latest reply: Oct 4, 2017 5:26 AM by zahid Rahim

# Analysis with first till todate Best Approach

Dear Eperts,

Suppose I have below data.

TrDate      Item          Category          Quantity

01SEP17    1                    A                    10

02SEP17    1                    B                    10

03SEP17    1                    C                    10

04SEP17    1                    A                    10

05SEP17    1                    B                    10

06SEP17    1                    C                    10

07SEP17    1                    A                    10

08SEP17    1                    B                    10

Now if i select TrDate in filter as 05SEP17 it is adding quantity perfectly to 50 with some expression "Sum({<TRDATE = {"\$(='<=' & Date(Max(TRDATE)))"}>} QUANTITY)"and even show category wise total as well like below:

A          20

B          20

C          10

But As we have only one transaction in category B on 05SEP2017 and being the TrDate filter applied on 05SEP17, I can only select category B Others are grey out if i forcefully select it effect on other filters. I want to give filter on category and other dimensions as well so that user can see total for category A and C as well.

What is the best approach to handel such development as most of the analysis require this and i have millions of records.

@stalwar1

Regards,

Zahid Rahim

• ###### Re: Analysis with first till todate Best Approach

you can try like this

Sum({<TRDATE = {"\$(='<=' & Date(Max(TRDATE)))"},TRDATE >} QUANTITY)

• ###### Re: Analysis with first till todate Best Approach

Dear Kushal,

Thank you for the reply but i have informed the issue with this approach. Individual dimensions which dont have records at the selected date in filter are not select able for further filters.

Regards,

Zahid Rahim

• ###### Re: Analysis with first till todate Best Approach

What is there in your report? What are the dimension? which dimension you are not able to select?

• ###### Re: Analysis with first till todate Best Approach

All the dimensions which don't have record in the selected date in filter are showing values but in a filter on dimension those are grey out.

• ###### Re: Analysis with first till todate Best Approach

Using set analysis which i suggested, you can select the dimension from the straight table.

• ###### Re: Analysis with first till todate Best Approach

Hi Zahid,

You could consider creating a calendar object to put your date value into a variable and use that variable in your set analysis instead of selecting on the date field itself.

cheers

Andrew

• ###### Re: Analysis with first till todate Best Approach

In the attached QVF. We able to see segment wise closing balance from first date till 5th Sep-2017.

The real problem is  As we dont have records for B and C on 5th Sep2017. The date selected 05th Sep2017 does not allow to filter only B and C segment.