Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
zahidrahim_ocp
Partner - Creator
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
Kushal_Chawda

you can try like this

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

zahidrahim_ocp
Partner - Creator
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

Kushal_Chawda

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

zahidrahim_ocp
Partner - Creator
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.

Kushal_Chawda

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

effinty2112
Master
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

zahidrahim_ocp
Partner - Creator
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.