topic Re: bad file name in QlikView

bad file name

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

trying to build "Sales Variation" same as the demo sample data in Qlik Sense: "Sales Discovery" > "Sales Comparison"

this is the expression in the sample data:

expression:

(sum([YTD Sales Amount])-sum([LY YTD Sales Amount]))/sum([LY YTD Sales Amount])

i follow the same structure on my report and i get bad file name, my expression i use:

(sum([Date.autoCalendar.InYTD Gross Earnings or Refunds])-sum([Date.autoCalendar.YearsAgo Gross Earnings or Refunds]))/sum([Date.autoCalendar.YearsAgo Gross Earnings or Refunds])

"Gross Earnings or Refunds" field in database

please advice what i'm doing wrong

thanks in advance

Re: bad file name

Oleg_Troyansky — Fri, 20 Oct 2017 16:48:44 GMT

You are likely getting a "bad field name" error - because you combined two separate field names into one, and it's causing the syntax error. In the Sales Discovery sample application, [YTD Sales Amount] is a field, not a combination of "YTD" and "Sales Amount". [LY YTD Sales Amount] is another field (I must say that this is a very much outdated practice to keep current YTD and prior YTD dales amounts in separate fields).

Today we typically use Set Analysis for the same calculations or multiply Sales by the conditional flag that can be 0 or 1. SO, a possibly correct way of calculating the same would be:

(sum([Date.autoCalendar.InYTD] * [Gross Earnings or Refunds])

-sum([Date.autoCalendar.YearsAgo] * [Gross Earnings or Refunds]))

/sum([Date.autoCalendar.YearsAgo] * [Gross Earnings or Refunds])

Assuming that YearsAgo is the calendar flag for Prior YTD (I think there is a better flag for that purpose...)

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Re: bad file name

Anonymous — Sat, 21 Oct 2017 17:07:28 GMT

Hello Oleg, Thanks so much for your replay.

i try your suggestion , it did not sow the error of bad file name but the result is wrong, BTW i change the field "Gross Earnings or Refunds" to "Units" field:

(sum([Date.autoCalendar.InYTD] * [Units])

-sum([Date.autoCalendar.YearsAgo] * [Units]))

/sum([Date.autoCalendar.YearsAgo] * [Units])

i am attaching screenshot for the result , as you can see in the screenshot the result is wrong and some result field are empty, i also like to have negative result show in red color

Re: bad file name

Oleg_Troyansky — Sun, 22 Oct 2017 15:36:12 GMT

Hi Uriel,

it's hard for me to troubleshoot without seeing your application. If you could share a small sample, I'd be able to take a look...

cheers!

Re: bad file name

Anonymous — Sun, 22 Oct 2017 15:42:27 GMT

Hello Oleg,

Thanks for your replay. How i can share sample? send you the csv file?

cheers!

Re: bad file name

Anil_Babu_Samineni — Sun, 22 Oct 2017 16:00:32 GMT

Is there Any error in your expression? If so, can you please share the image of expression

Re: bad file name

Anonymous — Sun, 22 Oct 2017 16:03:29 GMT

no error in the expression, the result in ""Sales Variation" are wrong

Re: bad file name

Oleg_Troyansky — Mon, 23 Oct 2017 16:42:13 GMT

No, CSV file won't help me... you can attach your QVF file to your post (use Advanced Editor to enable attachments)

Just noticed that your post is misplaced - it's a Qlik Sense question in a QlikView forum...