topic Re: Question on technical feasibility in QlikView

Question on technical feasibility

Tue, 18 Mar 2014 14:17:42 GMT

Hi All,

World be grateful if you can go through my below requirement and shed some light on the technical feasibility in QV

Details.

Objective is to Show the item availability across stores / brands etc.

Flow 1:

1) Select a week , say W10 2014
2) Opening Stock of a week is calculated as rolling stock. meaning say I select week 10 – 2014 stock number will be sum ( movement quantities ) where date <= 02-03-2014 (02-03-2014 is the last day of week 9) ( please note the stock fact is very well linked in the data model)
3) Now find all the items whose stock ( calculated as above) > 0 and count

I understand that this can be very well achieved fixing date range while loading and pre populating the items with a flag to indicate if the stock exists or not.

My question is, is there a way to make this dynamic to give user an option to select any week on the UI?

Please let me know.

Re: Question on technical feasibility

Tue, 18 Mar 2014 15:15:51 GMT

Hey Jayaprakash,

I would think that this can only be done on the front end as the user can select any input. Creating a flag on the back-end wont work.

I would create a variable that would calculate the appropriate date when W10 2014 is selected and then use that variable in the expression to find the rolling stock using set analysis.

ex:

Sum(<{Date={"<=$(vVariable)"}}>StockQuantity)

Hope it helps

Thanks

Re: Question on technical feasibility

Tue, 18 Mar 2014 16:18:22 GMT

Hi AJ thanks for the reply .

in this case how do I get the total count of items with stock >0 ? is the above expression work when I summarize the item count at a higher level dimension ? say item count under a brand

Re: Question on technical feasibility

Tue, 18 Mar 2014 16:39:44 GMT

May be you can add another expression like(The label for above expression is say StockQty):

Count(Distinct if(StockQty>0,Item#))

In table expressions you can use other expression labels in your calculations. This is a very effective way to calculate in cases like these.

Re: Question on technical feasibility

Wed, 19 Mar 2014 01:34:50 GMT

is Count(Distinct if(StockQty>0,Item#)) work when i dont have item as dimension ?

Re: Question on technical feasibility

Wed, 19 Mar 2014 02:20:46 GMT

Well you might have to aggregate it with Item#. Again its definitely doable.

These are all suggestions to show you how it can be done. When actually doing it you might have to change it as per needs.

Re: Question on technical feasibility

Wed, 19 Mar 2014 03:32:16 GMT

Hi , if doable i am happy can you please guild me . a sample would do .

Re: Question on technical feasibility

Wed, 19 Mar 2014 03:32:53 GMT

basically i want to know hot to 'aggregate it with Item#' as you said

Re: Re: Question on technical feasibility

Wed, 19 Mar 2014 16:44:25 GMT

Attached is an example.

Thanks

Re: Re: Question on technical feasibility

Wed, 19 Mar 2014 16:49:17 GMT

hey cant find the attachment

Re: Re: Re: Question on technical feasibility

Wed, 19 Mar 2014 16:51:23 GMT

How about now.

Re: Re: Re: Question on technical feasibility

Wed, 19 Mar 2014 17:05:54 GMT

nop ,no luck , could you please email to jayaprakashpk (at) rediffmail.com ?

Re: Re: Re: Question on technical feasibility

Wed, 19 Mar 2014 17:16:35 GMT

I have emailed it. Please take a look at it and let me know.