topic Re: Expression to find the next minimum value in QlikView

Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 07:16:50 GMT

Hi All,

I have a below table

MonthNo	Product	Sales
Jan-13	A	12
Feb-13	A	45
Mar-13	A	76
April-13	A	34
Feb-13	B	98
Mar-13	B	54
Apr-13	B	65

I am storing the Minimum MonthNo value in a 'vMonthNo' variable.

When I do "if(MonthName_Vol_Chart =vMonthNo,Sales,'') to get the Sale value for starting month.

I get the value for Product A as 12 but I do not get any value against Product B and I understand that it's because there no value for product B.

How can I make the expression flexible enough to get the values for the product B.

Note: There can be many Products like B which might have the same behaviour.

Thanks in advance,

Padmanabhan

Re: Expression to find the next minimum value

PrashantSangle — Mon, 21 Sep 2015 07:22:42 GMT

Hi,

Can you post sample app also provide your expected output.

Regards

Re: Expression to find the next minimum value

martinpohl — Mon, 21 Sep 2015 07:33:56 GMT

take this expression:

sum( if( aggr(nodistinct min(MonthNo), Product) = MonthNo, value, 0))

unfortunately, set analysis doen't work at this problem.

Regards

Re: Expression to find the next minimum value

settu_periasamy — Mon, 21 Sep 2015 07:44:23 GMT

hi,

may be try this..

Create the variable like

vMonthNo : aggr(Min(MonthNo),Product) //(don't give equal sign)

Expression

=if(MonthNo=$(vMonthNo),Sales,'')

Re: Expression to find the next minimum value

tresB — Mon, 21 Sep 2015 07:53:25 GMT

Use expression like:

=FirstSortedValue( Sales, MonthName_Vol_Chart)

With Product in dimension in chart, you would get the sale value for it's earliest date;

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:43:48 GMT

Hi All,

I do not get the value points with the suggestions provided.

Please find the attached qvw. I want the line graph to show the values on the data points of starting month and ending month.

Regards,

Padmanabhan

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:44:27 GMT

Forgot the attachment.

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:45:32 GMT

Hi Tresesco,

Thanks for your response, but it didn't work.

I have attached the qvw for your reference.

Regards,

Padmanabhan

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:48:41 GMT

Hi Max,

I have attached the qvw for your reference.

Regards,

Padmanabhan

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:50:44 GMT

Thanks for your response, but it didn't work.

I have attached the qvw for your reference.

Regards,

Padmanabhan

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:51:01 GMT

Hi,

Thanks for your response, but it didn't work.

I have attached the qvw for your reference.

Regards,

Padmanabhan

Re: Expression to find the next minimum value

tresB — Mon, 21 Sep 2015 08:51:13 GMT

PFA

You have to convert the Date field into a proper qv date field(numeric at the backend)

Re: Expression to find the next minimum value

padmanabhan_ram — Mon, 21 Sep 2015 08:55:33 GMT

Hi Tresesco,

When I try to transform it to Line graph, data doesn't show up.

Regards,

Padmanabhan

Re: Expression to find the next minimum value

settu_periasamy — Mon, 21 Sep 2015 08:57:47 GMT

Hi,

Check this..

Re: Expression to find the next minimum value

simenkg — Mon, 21 Sep 2015 08:58:50 GMT

How about doing it in the script:

when you load your fact-table:

Fact_t2:

Load

Autonumber(Year(%DateKey)&'-'&Month(%DateKey), Product) as MonthCounterPerProduct,

resident Facts order by %DateKey asc;

You have to first load the facts from file and then load them resident, as you cannot change the order of the table in Qlik on the first reading from file. If the dates are ordered in the original file, then you can do the operation on the original load.

Then you can use

sum({<MonthCounterPerProduct={1}>} Sales)

Re: Expression to find the next minimum value

tresB — Mon, 21 Sep 2015 09:02:38 GMT

Which chart? Try combo chart.