topic Re: Set Analysis Syntax Problem - Previous Quarter in QlikView

Set Analysis Syntax Problem - Previous Quarter

Dayna — Mon, 15 Mar 2021 11:21:02 GMT

Hello All!

I'm trying to get the previous quarter, and I think my syntax is slightly out... Here's what I have:

=sum({$<Applicator_Date={"$(=date(addmonths((Applicator_Date),-3)))"}>}Applicator_Cost)

Applicator_Date is a date field. Any ideas?

If I do this in a text box and change it to:

=date(addmonths(max(Applicator_Date),-3)) then I get the result I'd expect for the max date in the calendar, so I'm not sure what I'm missing....

Thanks!

Re: Set Analysis Syntax Problem - Previous Quarter

chrismarlow — Mon, 15 Mar 2021 19:15:03 GMT

Hi,

The set expression is calculated once for the object, i.e. if that is a table is the same for every row.

If you want to show a date, current month and prior quarter (meaning 3 months ago, or meaning accounting quarter end, not sure I understand your data) you could use something like ABOVE function with a suitable offset;

Cheers,

Chris.

Re: Set Analysis Syntax Problem - Previous Quarter

yassinemhadhbi — Mon, 15 Mar 2021 22:00:24 GMT

Good morning

The best idea is , to create quarter Field in the script 'Q' & Ceil(Month([Order Date])/3) As Quarter,

Then create Another column that Has : autonumber('Q' & Ceil(Month([Order Date])/3)) as QuarterNum

Then in the set analysis you will just do

sum({<QuarterNum={'$(=max(QuarterNum)-1)'}>}Applicator_Cost)

Re: Set Analysis Syntax Problem - Previous Quarter

Denisg — Mon, 15 Mar 2021 22:11:34 GMT

Hi,

In case you don't want to have a calendar with a Quarter field as described by Yassinemhadhbi , you can create a dummy date, 3month after.

Re: Set Analysis Syntax Problem - Previous Quarter

Dayna — Tue, 16 Mar 2021 12:23:25 GMT

Hi All,

The above isn't bringing back the results I'd expect, here's an example with the new calculation as per this thread:

Here's what I would expect...

Hopefully the above makes sense to what I'm trying to achieve?

Re: Set Analysis Syntax Problem - Previous Quarter

Denisg — Tue, 16 Mar 2021 12:34:32 GMT

Hi,

Above(Value) as the expression of the « Expected Value » column should do the trick.

Re: Set Analysis Syntax Problem - Previous Quarter

Dayna — Tue, 16 Mar 2021 14:08:00 GMT

Hi @Denisg ,

How would you do that in a pivot table where the dimension is on the left hand side, but the quarter timescale at the top? That's where I'd imagine the set analysis one would only look at the previous quarter for the item in question.

Thanks,

Re: Set Analysis Syntax Problem - Previous Quarter

Denisg — Tue, 16 Mar 2021 16:22:14 GMT

Hi,

Not sure I understand.

can you please design that table like you did above?

Re: Set Analysis Syntax Problem - Previous Quarter

Dayna — Tue, 16 Mar 2021 16:37:28 GMT

Yes of course, I've achieved the below so far using PEEK() within the load script, but at the time I thought the Set Analysis approach would be 'safer':

So this is a pivot, with the quarter as the dimension at the top, whereas the middle expression should be the value for the previous quarter. This is working using peek in the load script as per:

if(Applicator_ID=peek(Applicator_ID) , peek(Applicator_Cost),null()) as Applicator_PreviousQuarter

However, I was hoping to get the set analysis approach working too!

Re: Set Analysis Syntax Problem - Previous Quarter

chrismarlow — Tue, 16 Mar 2021 16:39:32 GMT

Hi,

There are functions Before and After that work in Pivot Table like Above and Below work in Straight Table.

Cheers,

Chris.

Re: Set Analysis Syntax Problem - Previous Quarter

Denisg — Tue, 16 Mar 2021 22:18:49 GMT

Hi,

You will find in this attached qvw an example of a Before function with this structure of data. See the Quarter offset on the column headings.

Hope this helps

Cheers

Denis

Re: Set Analysis Syntax Problem - Previous Quarter

Dayna — Wed, 17 Mar 2021 09:57:04 GMT

Lovely, thank you! Sorry, can you confirm the place to look for the Quarter offset? I couldn't see this! 🙂

Re: Set Analysis Syntax Problem - Previous Quarter

Denisg — Wed, 17 Mar 2021 10:38:33 GMT

Hi Dayna,

You see here that the right chart is showing a Before Expression. In yellow, the Quarter 2018-Q2 is displaying the data from the left chart in 2018-Q1 (purple)

Re: Set Analysis Syntax Problem - Previous Quarter

Dayna — Wed, 17 Mar 2021 13:12:50 GMT

Thanks for all your help!!