topic Re: Previous Fiscal Year data does not show up when i select date from calendar in QlikView

Previous Fiscal Year data does not show up when i select date from calendar

Fri, 09 Sep 2016 20:53:22 GMT

Hi all,

Wonder if you can help me.

I have a chart that calculates my sales of previous year using expression

Sum({$<Year_Fiscal_Order = {'$(=max(Year_Fiscal_Order)-1)'}>}[Sales Amount])

When i make a date selection from my calendar ([Invoice Date]) no values are showing for previous year.

Can someone help please?

Product	Current Year	Previous Year
Milk	200	0
Bread	130	0
Tea	60	0
Coffee	80	0
Butter	30	0
Water	900	0
Fish	20	0
Meat	16	0
LOC	2	0

In my calendar load, I have created a separate table and left joined where it auto numbered all my fiscal year in ascending order and i left joined that to the main calendar. It works perfectly fine just that when i select dates, then it goes off.

Regards,

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Fri, 09 Sep 2016 21:28:47 GMT

May be like this:

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, [Invoice Date]>}[Sales Amount])

Re: Previous Fiscal Year data does not show up when i select date from calendar

Fri, 09 Sep 2016 21:42:31 GMT

Sunny that does make my expression to respond, but i am expecting the expression to only return values for the dates i have selected but for previous fiscal year. this is ignoring my dates selections completely and just showing me previous year selections

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Fri, 09 Sep 2016 21:53:08 GMT

I think it would be better if you can use Date instead of Year field to get this working. If possible, can you share a qvw file where you are trying this out?

Re: Previous Fiscal Year data does not show up when i select date from calendar

Sat, 10 Sep 2016 00:22:29 GMT

Hi Sunny,

It will be hard to share customer data but i have created a app and replicated my issue.

Basically the expressions are calculating the totals with set Analysis based on Fiscal year but when i select a default calendar year the previous year stops working.

Cant think of a way to solve this problem.

Regards,

Sarb

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Sat, 10 Sep 2016 11:40:56 GMT

All you need to do is ignore any date related field where you plan to make a selection. For instance, If you think you will be making selections in Year_Fiscal and MonthYear field, then try this:

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, Month = {"=Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order))}>}[Amount]) > 0"}, Year_Fiscal, MonthYear>}[Amount])

The straight table below is using the new expression and that is why it is able to show 78 for previous column.

You can continue to add all Date related field like this

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, Month = {"=Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order))}>}[Amount]) > 0"}, Year_Fiscal, MonthYear, Year_Calendar, Month_Order, NumMonth, NumMonthYear>}[Amount])

Re: Previous Fiscal Year data does not show up when i select date from calendar

Sat, 10 Sep 2016 22:26:06 GMT

Hi Sunny,

Thanks for reply.

I can see that the expression is ignoring the selections of my date but what i wanted was if no dates are selected then it should respond to fiscal year calculation, but a user selects date, then it should look for amount in previous year for the selected dates.

At the moment, my expression is only locked to previous fiscal year hence it is only responding to that and when i select dates and my previous year doesnt show anything.

Is there a way i can tweek the expression to show fiscal year by default and if a user selects dates then it should compare with dates of previous year?

Regards,

(Attached is the same which shows what i described above.)

Re: Previous Fiscal Year data does not show up when i select date from calendar

vishsaggi — Sat, 10 Sep 2016 22:52:06 GMT

Hello Sunny the one highlighted in red below can you confirm ?

Is that used to get the Amount for Actual ?

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, Month = {"=Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order))}>}[Amount]) > 0"}, Year_Fiscal, MonthYear>}[Amount])

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Sat, 10 Sep 2016 23:22:54 GMT

Yes, that is what it is there for

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Sun, 11 Sep 2016 05:12:47 GMT

Your chart is on Month level and you are looking to see Date comparison when a single date is selected? I think I am not 100% sure of what exactly are you looking to do. May be provide us the expected output in an Excel file based on different selections

Re: Previous Fiscal Year data does not show up when i select date from calendar

Sun, 11 Sep 2016 09:07:36 GMT

Hi Sunny,

Please see the attached spread sheet.

The first table shows values when i select year 2015 and Jul Aug Sept and compares them with previous year of 2014.

On the table below is a default comparison of of what the table would do. This compares Current Fiscal year with Previous Fiscal year.

I am expecting to see this in one expression. Is that possible?

Regards,

Sarb

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Sun, 11 Sep 2016 11:43:47 GMT

Where did Product dimension came in from? The attached sample has no product dimension. Can you provide an update sample or an expected output based on the sample you have already provided?

Re: Previous Fiscal Year data does not show up when i select date from calendar

Mon, 12 Sep 2016 07:48:15 GMT

Sunny,

Now attached.

I thought i had attached the product dimension app in there.

Regards,

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Mon, 12 Sep 2016 08:30:52 GMT

I still don't see the Product dimension in the attached application

Re: Previous Fiscal Year data does not show up when i select date from calendar

Mon, 12 Sep 2016 08:40:13 GMT

Hi Sunny,

Ok that's because the app name is same and on qlik community it just gets overridden by the old one.

Now attached with a different name.

Regards,

Sarb

Re: Previous Fiscal Year data does not show up when i select date from calendar

sunny_talwar — Mon, 12 Sep 2016 09:14:19 GMT

Here you go...

Expression

Sum({$<InvoiceDate = {"$(='>=' & Date(AddYears(Min(InvoiceDate), -1), 'DD/MM/YYYY') & '<=' & Date(YearEnd(Max(InvoiceDate), -1, 5), 'DD/MM/YYYY'))"}, $(=Concat({<$Table = {CALENDAR}, $Field -= {InvoiceDate, Month}>}'[' & $Field & ']', ', '))>}[Amount])

When 2015 & Jul, Aug, Sept is selected

When 2016-17 is selected