Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

Set Analysis Syntax Problem - Previous Quarter

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!

2 Solutions

Accepted Solutions
Denisg
Contributor III
Contributor III

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

View solution in original post

Denisg
Contributor III
Contributor III

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)

Denisg_0-1615977394889.png

 

View solution in original post

13 Replies
chrismarlow
Specialist II
Specialist II

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;

20210315_1.png

Cheers,

Chris.

yassinemhadhbi
Creator II
Creator II

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)

Best Regards
Yassine Mhadhbi
Denisg
Contributor III
Contributor III

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. 

Denisg_0-1615846281614.png

 

Dayna
Creator II
Creator II
Author

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:

Dayna_0-1615897375781.png

Here's what I would expect...

Dayna_1-1615897394328.png

 

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

Denisg
Contributor III
Contributor III

Hi,

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

 

Dayna
Creator II
Creator II
Author

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,

 

Denisg
Contributor III
Contributor III

Hi,

Not sure I understand.

can you please design that table like you did above?

Dayna
Creator II
Creator II
Author

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':

Dayna_0-1615912574140.png

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! 

chrismarlow
Specialist II
Specialist II

Hi,

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

Cheers,

Chris.