Unit Cost Trends by Item

[siva0606]

Hi all,

I am new to Qlikview.

1) I need to put a chart on "UnitCost Trends by ItemName" .

The available fields are ItemName, UnitCost, Year, Month and Day.

2) I also need to analyse which item has Purchased more time.

Please help me in building the above two charts.

Regards,

Sivasu

[jvitantonio]

Hello,

1) Use Day and ItemName as Dimensions and use sum(UnitCost) in your expressions.

2) You can create a straight table with ItemName as dimension and count(ItemName) as expression. Then sort this column Descending.

JV

BI Experience | A place to share our Business Intelligence experiences

[datanibbler · Accepted solution]

Hi Sivasubramanian,

so you have some kind of base table with these fields? May I assume that in that table, there is one line for every item purchased/sold?

In that case, build a barchart/ combochart with

- dimension "day" (you might consider using a master_calendar)

- expression "UnitCost" - you can just make an expression_displayed_as_line to visualize the trend.

- expression "COUNT(UnitCost)" - that will tell you the nr. of items you have (sold/purchased) on a given day.

=> Use two y-axis (one left, one right) to avoid problems with the scale

=> Go from there and build up your chart any way you want to. QlikView gives you all the possibilities - well, a great many anyway.

HTH

Best regards,

DataNibbler

[siva0606]

Thank you so much Nibbler.

I builded the two charts.

But how i will see the chart by ItemWise? Whether i want to include the itemname in dimension?

and also I want to analyse which item has been purchased frequently?

Regards,

Sivasu

[datanibbler]

Hi,

you might just add a list_box "item" where you can select - the chart should automatically adapt.

Analyzing which items have been purchased a certain nr. of times is a tad more advanced.

The easiest way of doing this would be a separate chart, probably a table_chart of some sort would be best - i guess you want the full item_name to do this?

I'm going to have a bite to eat now - if I come back and there are no more answers, I will try to help you if I have some time.

Best regards,

DataNibbler

[siva0606]

Thanks Nibbler

Regards,

Sivasu

[datanibbler]

Hi,

I have one or two "category-based" charts like what you want. So:

- You need the nr. of items sold per category as a native field (calculated on script level), so you have to aggregate your table at some point. You can look it up, aggregation is easy, though it's always possible to get it wrong - so always use "EXIT SCRIPT;" right afterwards and first test if it runs up to there, then go on.

- (You can make an inline table (via the wizard, nothing easier (main_menu "Insert" in the script_editor)) with the categories (a from-value and a to-nr for each)

-> Make an IntervalMatch-LOAD statement like the following:

IntervalMatch (ÜS)
LOAD * INLINE [
    Start, End
    1, 10
    11, 20
    21, 30
    31, 40
    41, 50
    51, 70
    71, 90
];
(where ÜS is the native field, in my instance the nr. of overtime_hrs an employee has amassed - the intervals can of course be neg., but that wouldn't make sense in your scenario - read up on the IntervalMatch() fct., it's a bit tricky.

=> Then build your chart (as I said, a table chart would probably suit you best, with only that category as dimension - or you can have a date, but then your intervals might have to be corresp. lower.

HTH

Best regards,

DataNibbler