bad file name

[Anonymous]

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

[Oleg_Troyansky]

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

[Anonymous]

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

[Oleg_Troyansky]

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!

[Anonymous]

Hello Oleg,

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

cheers!

[Anil_Babu_Samineni]

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

[Anonymous]

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

[Oleg_Troyansky]

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...