topic Require help in set analysis in QlikView

Require help in set analysis

rajtechnocraft — Sat, 01 May 2010 12:35:34 GMT

Dear All,

I require help in set analysis

I have selection filters available as Year Month City in my application

I have developed pivot report which has contents :

Fields Year Month City

Calculated Columns: Quantity,Amount

What i want when i select any Year ,Month combination then my pivot should display value for current Year/Month selection as well as Previous Month also .

I have used the following set analysis:

Current Selection: (sum({1<ci.insertion_date={">=$(=monthstart(vMaxDate)) <=$(=vMaxDate)"}>} ci.received_quantity)

where vMaxDate is a variable which stores my date field (ci.insertion_date)

PreviousMonth: sum({1<ci.insertion_date={">=$(=monthstart(vMaxDate,-1)) <=$(=AddMonths(vMaxDate,-1))"}>} ci.received_quantity))

Above two expressions are calculating values but they are not matching with my pivot table.

Is there any error in formula.

Guys please help me .I require it urgently

Regards

Arvind

Require help in set analysis

Sat, 01 May 2010 15:06:46 GMT

Hi Arvind

some questions
- will you ever also filter on city? If so you can not use {1< as this will ignore any selections
- why do you use a set expression for the current results? This will be your natural result by the users selections?
- can users select a single date only or also a range of dates over several month's?
- which values do not match, can you provide an limited example?

Juerg

Require help in set analysis

Oleg_Troyansky — Mon, 03 May 2010 00:51:05 GMT

Agree with all questions that Juerg asked... in addition - I think your pivot table is showing number for the whole prior month, while your set analysis formula only filters data from the beginning of prior month to the current date a month ago (part of the month).

I also don't think that you should be using Set 1 - that will cause disregarding all other selections.

Lastly, when you are using variables to hold dates, you should be sensitive to formatting. I typically store the "number" behind the date in a variable, and then convert the value before using it. Your variable might be holding a formatted string, and then some of the date calculations might go wrong...

cheers,