topic Mutations each month in QlikView

Mutations each month

Mon, 21 May 2012 11:04:17 GMT

Dear reader,

I have this, probably, very simple question. I made a simple Excel Table to explane my problem.

Each month goods come in to my store and goods are beeing sold. In my Excel table I can see what I have in stock at the first day of the month and what I have in stock at the last day of the month. What I would want to build in Qlikview is a display were I can click on, for example, Februari and then it shows me, for only that month, what percentage of the goods in stock at 01.01.2012 were sold to Buyer A. So Qlikview should look at the first value in the row 'in stock' in Februari, then count the total amount of goods sold to buyer A in in the row 'sold' (in Februari) en then devide that two amounts, to create a percentage. Hope I made my problem clear.

I'm looking forward to reading your comments on my question.

Mutations each month

Mon, 21 May 2012 12:53:10 GMT

Hi,

You can use set analysis here and do it very simply.

I am assuming that user will select a month.

then use

sum( {<Date = {"$(=min(Date))"} >} [In Stock]) / sum(Sold)

This should give you required results. Just let me know, if any issue.

Thanks

Ashutosh Paliwal

Mutations each month

Mon, 21 May 2012 14:46:33 GMT

Thank you Ashutosh, your solution works perfectly. What could I change to the formula so I can also select multiple months (for example: Februari till Mai) and then see what the overall sold goods to buyer A were?

Greetings,

Rutger Jorna

Mutations each month

Mon, 21 May 2012 17:53:55 GMT

Hi,

I believe the same formula should work for that case also because here we are selecting the minumum possible date from the selections.

So, if you select feb to may then minimum possible date will be 1st feb, so our expression will take stock as on feb 1st and sold will be taken from all the availale data in sold field for feb to may.

So, this formula

sum( {<Date = {"$(=min(Date))"} >} [In Stock]) / sum(Sold)

will show

feb stock / sold stock in feb - may

if you just want to see the sold stock from feb to may then use sum(Sold) only which will show sold data according to selections and same way when selected Feb - May will show sold stock from Feb - May.

Hope this helps, Let me know if any issue.

Ashutosh Paliwal

Mutations each month

Thu, 31 May 2012 09:35:58 GMT

Hi Ashutosh,

Thank you for your reaction. You were right.

I was on a vacation during the last days, that was the cause of this late reaction.

Rutger Jorna

Mutations each month

Thu, 31 May 2012 09:43:12 GMT

Hi rutgerjorna,

It is okay.

Thanks

Ashutosh