topic Re: Alternate States for generating Last Year figures in QlikView

Alternate States for generating Last Year figures

Fri, 22 Apr 2016 12:55:23 GMT

Hi all@@,

I have created a trigger that when a date is slected it takes the date from last year as a selection in the alternate state.

It's a document trigger that has ONANYSELECT trigger.

This all works fine when no selection are made:

However when I select a field to limit the data by the LY figure instead sums everything for this year.

I have tried using Copy State contents but this just results in the alternate state and default state same being the exact same figures.

I have tried this Copy State trigger at the start before all the other triggers and at the end and it still doesn't work.

Has anyone else had this issue?

Is there a way around this or is this just a bug of Qlikview and Copy State not working correctly?

Please help, Lauren

Re: Alternate States for generating Last Year figures

Gysbert_Wassenaar — Fri, 22 Apr 2016 15:57:40 GMT

I don't understand what you're trying to do. Why do you think you need to use alternate states and the black magick of triggers?

Re: Alternate States for generating Last Year figures

Sun, 24 Apr 2016 22:03:36 GMT

When I select a date periods say 01/01/2016-05/01/2016 the alternate state then selects the year before dates says 01/01/2015-05/01/2015. I do this because i want to show the user This years revenue vs. Last years revenue.

Please let me know if you need me to elaborate, sorry if im being vague.

And thanks for getting back to me!

Re: Alternate States for generating Last Year figures

qliksus — Mon, 25 Apr 2016 04:16:16 GMT

The CY vs LY can be achieved using the set anlaysis not sure why you need to go to alternate states to achieve that

Have a look at the below thread for CY vs LY

Year-over-Year Comparisons

Re: Alternate States for generating Last Year figures

Pulkit_Thukral — Mon, 25 Apr 2016 04:35:37 GMT

Maybe something like this :

Previous year Expression- sum( {$<Call_Year={"$(=DATE(FLOOR(YearStart(YearStart(Today())-1)),'YYYY'))"}>} CountID)

Current year Expression- sum( {$<Call_Year={"$(=DATE(FLOOR(YearStart(Today())),'YYYY'))"}>} CountID)

don't ignore rest date fields (i.e date=).

Also would request you to share sample data to exhibit the same logic using that data.

Re: Alternate States for generating Last Year figures

Mon, 25 Apr 2016 10:34:48 GMT

Thanks again for getting back to me.

I was advised that using set analysis is resource heavy.

Is this not the case?

Also at the moment I have TY vs LY using a column in my SQL database called LY_DTE which maps last years date.

However there are two other columns I will need for the user called LY_COM which a business set date and LY_DAY which maps last years date but on day of week instead.

Re: Alternate States for generating Last Year figures

Tue, 26 Apr 2016 12:52:24 GMT

Using this in the Last Year expression gives the correct answer:

(sum({$<TransactionDate = alt::TransactionDate, LYDTE, TransactionYear>}LegFare) + sum({$<TransactionDate = alt::TransactionDate, LYDTE, TransactionYear>}

ProductsValue))

Props to sunindia‌ for the answer