topic Re: Analysis with first till todate Best Approach in QlikView

Analysis with first till todate Best Approach

zahidrahim_ocp — Wed, 04 Oct 2017 05:53:43 GMT

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

Kushal_Chawda — Wed, 04 Oct 2017 07:14:23 GMT

you can try like this

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

Re: Analysis with first till todate Best Approach

zahidrahim_ocp — Wed, 04 Oct 2017 07:22:25 GMT

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

Kushal_Chawda — Wed, 04 Oct 2017 07:26:05 GMT

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

zahidrahim_ocp — Wed, 04 Oct 2017 08:22:32 GMT

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

Kushal_Chawda — Wed, 04 Oct 2017 08:53:19 GMT

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

Re: Analysis with first till todate Best Approach

effinty2112 — Wed, 04 Oct 2017 09:07:54 GMT

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

zahidrahim_ocp — Wed, 04 Oct 2017 09:26:34 GMT

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.