Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Compare a measure (sales) for an attribute (date)

Hi

I want to create a chart that allows a user to compare sales for two dates (current and previous) across region and country. Furthermore the user wants the freedom to change the current and previous dates. If my data looks like this:

 

RegionCountrySalesDateSales
AsiaAustralia1/08/2015105
AsiaAustralia2/08/201587
AsiaAustralia3/08/201534
AsiaAustralia4/08/2015200
AsiaNew Zealand1/08/201525
AsiaNew Zealand2/08/201556
AsiaNew Zealand3/08/201523
AsiaNew Zealand4/08/201554
AsiaJapan1/08/2015654
AsiaJapan2/08/201545
AsiaJapan3/08/2015432
AsiaJapan4/08/2015645
EuropeGermany1/08/2015321
EuropeGermany2/08/2015432
EuropeGermany3/08/2015867
EuropeGermany4/08/2015534

and the user selects 1/8/2015 as the previous date and 3/8/2015 as current date the chart will look something like this:

 

SalesPreviousCurrentDifference
AsiaAustralia10534-71
New Zealand2523-2
Japan654432-222
subtotal784489-295
EuropeGermany321867546
subtotal321867546
Total11051356251

and if the user selects 2/8/2015 as the previous date and 3/8/2015 as current date the same chart will look something like this

SalesPreviousCurrentDifference
AsiaAustralia8734-53
New Zealand5623-33
Japan45432387
subtotal188489301
EuropeGermany432867435
subtotal432867435
Total6201356736

I've been trying using a combination of

set analysis (for previous sales I have "=sum({<SalesDate={"=Date($(vPriorComparisonDate),'DD-MM-YYYY')"}>}Sales)"

a variable (vPriorComparisonDate) set by means of a calendar object (for user to select previous date)

a list box showing SalesDate (for user to select current date)

either a pivot table or a straight table

But values are not correct

I've attached the excel mockup for ease of reference

Any help would be much appreciated.

Thanks

Ross

1 Solution

Accepted Solutions
MVP
MVP

Re: Compare a measure (sales) for an attribute (date)

Not really sure if you want to mix default state with alternate states in your charts, but set identifier $ is referencing default state, so if you want the chart to consider only State A and the chart is set to inherited state and the sheet to state A, you need to remove the set identifier:

=sum({<Category={'SwapCurveRisk'}>}SpcValue)

View solution in original post

3 Replies
Not applicable

Re: Compare a measure (sales) for an attribute (date)

I made some progress using the following expression

sum({$<Category={'SwapCurveRisk'}>}SpcValue)

this worked perfectly until i introduced alternate states. i set sheet A to StateA. all the objects on the sheet to inherited. the other sheet (B) i set to StateB, again all objects on that sheet to inherited. reason being on sheet A users must select a ReportingDate (always one selected value=yes). sheet b i want  users to be able to perform time series analysis - e.g. sum(SpcValue) by Category across time (ReportingDate )

now, for some reason, the expression above is ignoring the ReportingDate selection on sheet A - it just sums up all the values no matter the ReportingDate selected.

please help

Re: Compare a measure (sales) for an attribute (date)

Is your ReportingDate Filter in StateA or InheritedState? If its in StateA, you can try this:

Sum({$<Category={'SwapCurveRisk'}, ReportingDate = StateA:: ReportingDate>}SpcValue)

MVP
MVP

Re: Compare a measure (sales) for an attribute (date)

Not really sure if you want to mix default state with alternate states in your charts, but set identifier $ is referencing default state, so if you want the chart to consider only State A and the chart is set to inherited state and the sheet to state A, you need to remove the set identifier:

=sum({<Category={'SwapCurveRisk'}>}SpcValue)

View solution in original post