Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Partner - Creator

## 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

7 Replies

you can try like this

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

Partner - Creator
Author

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

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

Partner - Creator
Author

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.

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

Master

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

Partner - Creator
Author

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.

Community Browser