Analysis with first till todate Best Approach

[zahidrahim_ocp]

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

[Kushal_Chawda]

you can try like this

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

[zahidrahim_ocp]

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

[Kushal_Chawda]

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

[zahidrahim_ocp]

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.

[Kushal_Chawda]

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

[effinty2112]

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

[zahidrahim_ocp]

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.