Expression to find the next minimum value

[padmanabhan_ram]

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

[padmanabhan_ram]

Hi,

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

I have attached the qvw for your reference.

Regards,

Padmanabhan

[tresesco]

PFA

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

[padmanabhan_ram]

Hi Tresesco,

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

Regards,

Padmanabhan

[settu_periasamy · Accepted solution]

Hi,

Check this..

[simenkg]

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)

[tresesco]

Which chart? Try combo chart.