Help with expression using multiple variables

[Anonymous]

Hi All,

Please I need help with some price analysis in QlikSense; I have an InvoiceSales table with product,quantitysold,actualrevenue and product category fields, and a SuggestedRates table with category and monthly rates.

InvoiceSales:
LOAD * Inline [
Product, Category, QuantitySold, ActualRevenue
Card,  Paper,   6,    3
Tissue, Paper,   23,   15
Poster, Paper,   14,   7
Spoon, Plastic, 9,   4
Cup,  Plastic,  18,   8
];

SuggestedRates:
LOAD * Inline [
Category, Jan-16, Feb-16, Mar-16
Plastic,  $0.655, $0.635, $0.685
Paper,    $0.620, $0.620, $0.638
];

My goal is to calculate the 'Estimated Revenue' and 'Revenue Variance' based on the suggested rates for the month selected(see attached sample qvf). So if Jan-16 is selected, 'Estimated Revenue' = (QuantitySold*$0.620) for Paper products, (QuantitySold*0.655) for Plastic products.

How can I achieve this? Is it possible to declare a variable for each month with multiple rates?

I appreciate the help!

[#2]

You may want to consider re-organizing your data to align it more dynamically with any chosen dimensions. Please consider:

SuggestedRates:

LOAD * Inline [

Category, RefDate, CostRate

Plastic,  Jan-16, $0.655

Plastic,  Feb-16, $0.635

Plastic,  Mar-16, $0.685

Paper,   Jan-16, $0.620

Paper,   Feb-16, $0.620

Paper,  Mar-16, $0.638

];

This will expose the data by date and category and will help avoid some challenging set logic to select a data rate...

Hope this helps...

[Clever_Anjos]

It would be much easier if you crosstable your model

InvoiceSales:

LOAD * Inline [

Product, Category, QuantitySold, ActualRevenue

Card,  Paper,   6,    3

Tissue, Paper,   23,   15

Poster, Paper,   14,   7

Spoon, Plastic, 9,   4

Cup,  Plastic,  18,   8

];

SuggestedRates:

CrossTable(Month,Value)

LOAD * Inline [

Category, Jan16, Feb16, Mar16

Plastic,  0.655, 0.635, 0.685

Paper,    0.620, 0.620, 0.638

];

[Anonymous]

Thanks Clever/Greg! The actual dataset is a lot larger than the sample and it's updated monthly with new rates so I suppose the CrossTable function would work for organizing the data.

That said, after the data reorg how do I go about calculating the 'Estimated Revenue' and 'Revenue Variance'?

[#5]

With the data exposed as a column, that column can now be used as a dimension, so you could create a chart object with the dimensions of 'RefDate', 'Product', and 'Category' (order as you see fit). Set your expressions to be:

'Estimated Revenue' =  sum(QuantitySold * CostRate)

'Actual Revenue' = sum(AcutualRevenue)

and 'Variance' = [Actual Revenue] / [Estimated Revenue] - 1 // view this as a percent with 2 fixed digits

This should lay out a flexible control and have QlikSense do the heavy lifting for you. You could even make a drill-down group (Category, Product, RefDate) for an active pivot table, etc.

Note: (We use QlikView, not QlikSense [yet] so my answers reflect a QlikView Document way of doing this analysis)

Hope this helps.

[Anonymous]

Thanks Greg, I'll try and let you know if this works.